Excel 2010: Save current tab as tab delimited txt file

I do most of my stats in R, thus I know nothing about Excel macros and must ask for help. My institute provides MS Office 2010 (currently), so I use that to do quick edits to my data sets, then I save them as tab limited .txt files and open them in R. I use .xlsx file format, because I can save several data sets in the same file. This makes it easier to keep track of different projects.

However, Excel is excel after all. Whenever I try to save as txt file it first asks me, if want to replace the earlier txt file I have created. Then it says that the file format can't support multiple tabs and I must click "OK". As if this wasn't enough, next it asks if I really want keep the workbook in this format...and finally, after clicking half a dozen times, I have my txt file, but now I have to close it, since I don't want to edit the txt file, since I would lose these edits in .xlsx file. Well, when I close Excel, it asks if I want to save my .txt file again!

Now after 5 years of reading these warnings almost every working day, I am sick of this. I gotta find a better system. It appeared to me that maybe a macro would help.

I would like the macro to save current tab of my .xlsx file as tab limited .txt file into the folder where the .xlsx file is located. I want it to use the name of the current tab as file name and overwrite any previous files with the same name. And I want to do this with one click.

Mikko

Posted 2011-12-14T23:57:50.043

Reputation: 153

Great question. I struggle with this too. Is there a better forum for this? Why the close vote? – None – 2011-12-15T03:37:15.963

Because xlsx package solves the problem better than any VBA macro. Just do read.xlsx("filename.xlsx", "tabname"). I was looking for this kind of package about a year ago. That time there was one, but it didn't work very well. This one seems to work better. I didn't know about it. – Mikko – 2011-12-15T15:14:36.183

Answers

What I'd do:

Save your data in Excel format.
Read it into R with xlsx, RODBC or one of the other packages out there that can handle Excel.
Do your analysis.

If you know nothing about Excel macros, this will be a lot easier than trying to learn VBA.

I'll also add: doing it this way means you don't have to worry about formatting issues. If you save a worksheet in text format, it retains any formatting that may have been applied, eg commas or dollar signs in numbers. This will trip up R's read.delim, which will assume that any non-numeric characters means the variable should be treated as a factor.

Hong Ooi

Posted 2011-12-14T23:57:50.043

Reputation: 256

Agreed. Much better than Excel macro. Thanks! – None – 2011-12-15T02:09:15.030

How about this to get the data into R, where you have more control over how you save to file?

In Excel: Ctrl-A, Ctrl-C (Select all in current sheet, copy to clipboard)
In R: x <- read.delim("clipboard")

Not exactly one click, but pretty simple nonetheless.

mdsumner

Posted 2011-12-14T23:57:50.043

Reputation: 101

This doesn't work together with Notepad++ and is clumsy with large data sets – None – 2011-12-15T02:10:40.100