3

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.

Improve this question
  • Great question. I struggle with this too. Is there a better forum for this? Why the close vote? – Aaron Dec 15 '11 at 3:37
  • 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 Dec 15 '11 at 15:14
3

What I'd do:

  1. Save your data in Excel format.
  2. Read it into R with xlsx, RODBC or one of the other packages out there that can handle Excel.
  3. 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.

Improve this answer
  • Agreed. Much better than Excel macro. Thanks! – Largh Dec 15 '11 at 2:09
0

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

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

Not exactly one click, but pretty simple nonetheless.

Improve this answer
  • This doesn't work together with Notepad++ and is clumsy with large data sets – Largh Dec 15 '11 at 2:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.