Home | Articles | Microsoft | Microsoft Office | How to Export all Excel Worksheets as CSV

How to Export all Excel Worksheets as CSV

I recently went on a hunt to try to figure out a way to export all my worksheets in an Excel document to CSV.  The need arose simply out of frustration.  Instead of copying cells and pasting as values into a new workbook, then saving as CSV, I wanted a simple and logical approach that could save me time and frustration.

Well, luckily a couple of folks hijacked an Excel thread and provided a simple Macro that can do the job quickly and easily.  Unfortunately, the original Macro dumped everything into the folder the Excel Workbook resided in. With a small modification, it now exports all CSV files into a sub-directory “CSV”.

Public Sub SaveAllSheetsAsCSV()
On Error GoTo Heaven

' each sheet reference
Dim Sheet As Worksheet
' path to output to
Dim OutputPath As String
' name of each csv
Dim OutputFile As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

' Save the file in current director
OutputPath = ThisWorkbook.Path

    Dim strDir As String
    strDir = OutputPath & "\CSV\"
     
    If Dir(strDir, vbDirectory) = "" Then
        MkDir strDir
    Else
    End If

If OutputPath <> "" Then
Application.Calculation = xlCalculationManual

' save for each sheet
For Each Sheet In Sheets

    OutputFile = OutputPath & "\CSV\" & Application.PathSeparator & Sheet.Name & ".csv"

    ' make a copy to create a new book with this sheet
    ' otherwise you will always only get the first sheet

    Sheet.Copy
    ' this copy will now become active
     ActiveWorkbook.SaveAs Filename:=OutputFile, FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
Next

Application.Calculation = xlCalculationAutomatic

End If

Finally:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

Exit Sub

Heaven:
MsgBox "Couldn't save all sheets to CSV." & vbCrLf & _
        "Source: " & Err.Source & " " & vbCrLf & _
        "Number: " & Err.Number & " " & vbCrLf & _
        "Description: " & Err.Description & " " & vbCrLf

GoTo Finally
End Sub

Credit to Alex, Graham, and Vivek for the original Macro

Enjoy

About Joe DiFiglia

I have always had a passion for everything computing. In early 2000, I decided to take my passion to the web. Thus, C.O.D. was born. Through the years we have made many great friends at C.O.D. and hope to continue our journey for years to come.

One comment

  1. Great solution – it worked pretty well for me. The only thing I would add, is that the Outputfile variable may specify illegal characters, as written. To work around this, I got rid of “OutputPath & “CSV” & Application.PathSeparator & …” , and left the output file name as sheet name only. This didn’t place the csvs in the right folder, however – just the active directory. This is the first time I have used Visual Basic. I’m sure there’s a more elegant and complete workaround.

Leave a Reply

Your email address will not be published. Required fields are marked *