Table of Contents
VBA can be used to automate the process of saving individual sheets in an Excel workbook as CSV files. To do this, you would need to write a macro that loops through all the sheets in the workbook and uses the SaveAs method to save each sheet as a CSV file. This can be a time-saving solution since you can save manually repeating the same action multiple times.
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:UsersbobbiOneDriveDesktop"
'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:UsersbobbiOneDriveDesktop"
'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.