Tag Archives: excel

Exporting individual sheets from an Excel workbook

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.

Sub Splitbook()
MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook.SaveAs _
Filename:= sht.Name & “.csv”,Ā FileFormat:=xlCSV
ActiveWorkbook.Close savechanges:=False
Next sht
End Sub

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.