try to save with existing file name" width="693" height="179" />Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.
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.
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
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.
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
When you save the file, if the folder contains a file with the same name the Excel will display a message like this.
try to save with existing file name" width="693" height="179" />
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
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
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.
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
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.
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
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.
