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.

Convert an Excel File to CSV Using VBA

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.

Warning message when try to save with existing file name

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.

Contact Form

Name

Email *

Message *