VBA: How to Save Sheets as CSV Files (With Example)


You can use the following syntax in VBA to save each sheet in a workbook to a CSV file:

Sub SaveCSV()

Dim Ws As Worksheet
Dim SaveDir As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat

'specify directory to save CSV files in
SaveDir = "C:\Users\bobbi\OneDrive\Desktop\"

'save each sheet to individual CSV file
For Each Ws In Application.ActiveWorkbook.Worksheets
    Ws.SaveAs SaveDir & Ws.Name, xlCSV
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True

End Sub

This particular macro will save each sheet in the currently active workbook to a CSV file.

The CSV files will be saved in the path specified in the SaveDir variable.

Note: The line Application.DisplayAlerts=False tells VBA to turn temporarily turn off any display alerts in Excel when saving the files.

The following example shows how to use this syntax in practice.

Example: Save Sheets as CSV Files Using VBA

Suppose we have an Excel workbook with two sheets.

The first sheet is called player_stats and contains statistics about various basketball players:

The second sheet is called team_info and contains information about various basketball teams:

Suppose we would like to save each of these sheets as individual CSV files on the Desktop of our computer.

We can create the following macro to do so:

Sub SaveCSV()

Dim Ws As Worksheet
Dim SaveDir As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat

'specify directory to save CSV files in
SaveDir = "C:\Users\bobbi\OneDrive\Desktop\"

'save each sheet to individual CSV file
For Each Ws In Application.ActiveWorkbook.Worksheets
    Ws.SaveAs SaveDir & Ws.Name, xlCSV
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True

End Sub

Once we run this macro, each sheet will be saved as a CSV file on our Desktop.

If I navigate to the Desktop on my computer I can see each of these individual CSV files with file names that match the sheet names:

If I open the player_stats CSV file using Notepad, I can see that the values from the Excel file have been saved as comma-separated values:

Note that in this example we were able to save two sheets in our workbook as individual CSV files, but this same macro will work with any number of sheets.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Number of Sheets in Workbook
VBA: How to Extract Data from Another Workbook
VBA: How to Add New Sheets

Featured Posts

One Reply to “VBA: How to Save Sheets as CSV Files (With Example)”

  1. Your syntax is awesome, exactly what I need, except that I don’t need the whole sheet but only a range of every sheet. Everything else is just what I need. What would you add to make it work as I need.
    Thank you

Leave a Reply

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