Windows – How to convert Excel file with multiple sheets to a set of CSV files

csvmicrosoft excelmicrosoft-officewindows

I have an Excel file with 20 sheets on it. Is there a way to quickly convert the file into 20 CSV files – one for each sheet?

I can do it manually of course, but I'll have to be processing these files a lot, so it would be great to have some kind of command line utility (or even Excel command line switches).

Best Answer

The method by vembutech is a definitely a more polished solution. Here is a lightweight VBA macro that would export all the sheets from one workbook.

Goes in the active workbooks folder all names are workbookname_sheetname.csv

Sub exportcsv()
Dim ws As Worksheet
Dim path As String

path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
For Each ws In Worksheets
    ws.Activate
    ActiveWorkbook.SaveAs Filename:=path & "_" & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Next
End Sub

EDIT:

For the comment. The original macro simply uses the save as function (which is sort of like closing the original). If you want to do this while leaving the original open you need to copy to a new workbook, save, close the new workbook.

Sub exportcsv()
Dim ws As Worksheet
Dim path As String

path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
For Each ws In Worksheets
    ws.Copy
    ActiveWorkbook.SaveAs Filename:=path & "_" & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close False
Next
End Sub