I received an Excel file containing several sheets that I needed to process individually in R and I was looking for a way to automate the process (as opposed to File -> Save As.. ~25 times). I came across this page and the method worked like a charm! I modified the script slightly to save as csv rather than xls, thanks to this link.
MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
Filename:= sht.Name & “.csv”, FileFormat:=xlCSV
I also found some posts about doing this within R, but one package that does this (xlsReadWrite — Windows only) has been removed from CRAN and the other (gdata) crashed my R session when I tried it 😦 As CRAN and many other sources advise, the best way to work with Excel files in R is to not work with them.
Note: In Excel for Mac 2011, the steps to run the script are:
– Go to Tools -> Macro -> VB editor
– Insert -> Module; paste the code above into the editor
– Select the code, hit the Run button.