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
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.



1 thought on “Exporting individual sheets from an Excel workbook

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s