Save Each Excel Worksheet To Separate CSV File Using VBA (Worksheet Name As File Name)

In the previous post we learnt how to convert an Excel sheet to CSV file. We discussed various techniques we can use when converting to CSV files such as how to change folder path, file name etc.

Convert an Excel File to CSV Using VBA

But there was one issue. If we use that method for an Excel file with multiple sheets, then the program will convert only the activesheet to a CSV file and the rest of the sheets will be deleted. So in this post I will show you how to convert each and every sheet of an Excel file to a separate CSV file. So stay focused.

First we need to give a name to our subroutine. Let’s name it as “ConvertEachSheetToCSV”

Sub ConvertEachSheetToCSV()

End Sub

We need a few variables to do this job. Let’s declare them as follows.

Dim WB As Workbook
Dim WS As Worksheet
Dim FolderPath As String
Dim FileName As String

Each sheet will be assigned to the WS variable while we loop through all the sheets of the Excel workbook. WB is to hold each workbook object until saved as a CSV file. FolderPath and FileName variables are to hold the path of the saving folder and name of each CSV file respectively.

We save all the CSV files inside one folder. So as the next step we assign the path of that folder to the FolderPath variable.

FolderPath = "D:\Work\New Post 2\Test"

Now we can loop through each sheet of the Excel file using a For Each ....Next statement.

For Each WS In Worksheets

Next WS

Then inside this For Each….Next statement we can get a copy of each sheet and save it as a CSV file.

WS.Copy

Set WB = ActiveWorkbook

FileName = WS.Name
WB.SaveAs FileName:=FolderPath & "\" & FileName, FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = False
WB.Close
Application.DisplayAlerts = True

We get the name of each sheet from WS.Name property and assign it to a variable “FileName”. Then we save the CSV file with that name. "Application.DisplayAlerts = False" is used to suppress the warning messages which occur when closing the CSV files. If "Application.DisplayAlerts = False" is omitted then Excel will show this warning message before closing each CSV file.

Then the user needs to intervene to close each file. However we can get rid of that by placing Application.DisplayAlerts = False before closing each file. So now we learnt how to save each worksheet as a separate CSV file with the sheet name as file name. Below is the complete code for your reference.

Sub ConvertEachSheetToCSV()

     Dim WB As Workbook
     Dim WS As Worksheet
     Dim FolderPath As String
     Dim FileName As String

     FolderPath = "D:\Work\New Post 2\Test"

     For Each WS In Worksheets

         WS.Copy

         Set WB = ActiveWorkbook

         FileName = WS.Name
         WB.SaveAs FileName:=FolderPath & "\" & FileName, FileFormat:=xlCSV, CreateBackup:=False
         Application.DisplayAlerts = False
         WB.Close
         Application.DisplayAlerts = True

     Next WS

End Sub

Suppose we ran the above macro for an Excel workbook which contains three sheets with names “Sheet1”, “Sheet2” and “Sheet3”.

Then it will create 3 separate CSV files (One file for each sheet) inside the given folder.

Overwriting existing files

But what will happen if there are CSV files with the same names inside the saving folder. For an example assume there is a CSV file called “Sheet1.csv” inside the saving folder already. Then Excel will display a message like this interrupting the running process.

So how do we solve this problem. Sometimes you may need to let the user decide whether he/she wants to overwrite the file or not. However if you need to overwrite the existing files without getting any warning message, change the placement of the “Application.DisplayAlerts = False” as follows.

Application.DisplayAlerts = False
WB.SaveAs FileName:=FolderPath & "\" & FileName, FileFormat:=xlCSV, CreateBackup:=False
WB.Close
Application.DisplayAlerts = True

Now the program will overwrite files without interrupting the running process.