In this post I will show you how to convert an Excel file to a CSV using VBA. This post explains various techniques you can use when converting to CSV. When we convert an Excel file to CSV, we can give a preferred name of our own or we can use the original sheet name as the file name. Also it is possible to save the file to a different directory by changing the folder path. Sometimes we need to overwrite the existing files as well. So in this post you can learn how to overwrite the existing CSV file automatically overcoming the warning message.
First let’s look at the simplest case scenario. Suppose we know the absolute file path of the new file we are saving. Then we can develop the subroutine as follows.
|
Sub ConvertToCSV() Dim WB As Workbook Dim FilePath As String Set WB = ActiveWorkbook FilePath = "D:\Work\Project Files\MyFile.csv" WB.SaveAs Filename:=FilePath, FileFormat:=xlCSV, CreateBackup:=False End Sub |
And if you have folder path in one variable and file name in another variable then you can slightly change the above subroutine like this.
|
Sub ConvertToCSV() Dim WB As Workbook Dim FolderPath As String Dim FileName As String Set WB = ActiveWorkbook FolderPath = "D:\Work\New Post 4" FileName = "Test File.csv" WB.SaveAs FileName:=FolderPath & "\" & FileName, FileFormat:=xlCSV, CreateBackup:=False End Sub |
When you save the file, if the folder contains a file with the same name the Excel will display a message like this.
If you select “Yes” then it will replace the existing file with the new file. Yet sometimes you may want to overwrite the file without the user's involvement. Fortunately there is a solution for that as well. You can use “Application.DisplayAlerts = False” to suppress that message. However, remember to set it to true after saving the file. Check below subroutine to understand how to use Application.DisplayAlerts
|
Sub ConvertToCSV() Dim WB As Workbook Dim FolderPath As String Dim FileName As String Set WB = ActiveWorkbook FolderPath = "D:\Work\New Post 4" FileName = "Test File" Application.DisplayAlerts = False WB.SaveAs FileName:=FolderPath & "\" & FileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False Application.DisplayAlerts = True End Sub |
In the above examples we gave our own name to the CSV file. Next let’s look at how to save the CSV file, giving sheet name as file name. To do that we need to get the file name using Activesheet.Name property. Then we can save the file using the same method.
|
Sub ConvertToCSV() Dim WB As Workbook Dim FolderPath As String Dim FileName As String Set WB = ActiveWorkbook FolderPath = "D:\Work\New Post 4" FileName = ActiveSheet.Name WB.SaveAs FileName:=FolderPath & "\" & FileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False End Sub |
Sometimes you may want to save the CSV file inside the same folder where the original Excel file is in. This also can be easily done with help of Workbook.Path property.
|
Sub ConvertToCSV_Ex5() Dim WB As Workbook Dim FolderPath As String Dim FileName As String Set WB = ActiveWorkbook FolderPath = WB.Path FileName = ActiveSheet.Name WB.SaveAs FileName:=FolderPath & "\" & FileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False End Sub |
Now we learnt how to convert an Excel file to a CSV file using VBA. But what if we have more than one sheet in the Excel file. If you run above macros for an Excel file with multiple sheets, macro will convert activesheet to the CSV file. Other sheets will be deleted automatically.
Also see
How To Quote All Cells Of A CSV File
Save Each Excel Worksheet To Separate CSV File Using VBA (Worksheet Name As File Name)
