How to Apply Macro to Convert Multiple Excel Files to CSV Files

One of the common problems that most of us face in our daily lives while working with Macro in Excel is how to convert multiple Excel files to CSV files. In this article, I’ll show you how you can accomplish this comfortably and conveniently with proper examples and illustrations.


Macro to Convert Multiple Excel Files to CSV Files (Quick View)

Sub Convert_Multiple_Excel_Files_to_CSV_Files()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)

File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Folder with the Excel Files"
If File_Dialog.Show <> -1 Then
    Exit Sub
End If

File_Path = File_Dialog.SelectedItems(1) & "\"
Destination_Path = File_Dialog.SelectedItems(1) & "\"

File_Name = Dir(File_Path & "*.xls*")
Do While File_Name <> ""
    Set File = Workbooks.Open(Filename:=File_Path & File_Name)
    Destination_Name = Destination_Path & Left(File_Name, InStr(1, File_Name, ".") - 1) & ".csv"
    File.SaveAs Filename:=Destination_Name, FileFormat:=xlCSV
    File.Close savechanges:=False
    File_Name = Dir
Loop

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

VBA Code to Develop a Macro to Convert Multiple Excel Files to CSV Files


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


An Overview to Convert Multiple Excel Files to CSV Files Using Macro (Step-by-Step Analysis)

So, without further delay, let’s go to our main discussion today. Here we’ve got a folder in our computer ExcelFiles that contains 5 Excel files called Book1.xlsx, Book2.xlsx, Book3.xlsx, Book4.xlsx, and Book5.xlsx.

Excel Files to Develop the Macro to Convert Multiple Excel Files to CSV Files

Our objective today is to convert the Excel files into CSV files using a Macro.

⧪ Step 1: Setting up the Environment

First of all, we’ll set some necessary parameters to the required values to set up an environment for running the code smoothly. These are:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Setting up the Environment to Develop the Macro to Convert Multiple Excel Files to CSV Files

⧪ Step 2: Selecting the Folder with the Excel Files

This is one of the most important steps. Each time the code is run, the user will get an opportunity to select the folder with the Excel files. We’ll use the VBA FileDialog object for this purpose.

Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)

⧪ Step 3: Allocating the FileDialog properties

Next, we’ll allocate the properties of the FileDialog object. One can’t select more than one folder. Also, if no folder is selected the code will stop automatically.

File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Folder with the Excel Files"
If File_Dialog.Show <> -1 Then
    Exit Sub
End If

FileDialog Properties to Develop the Macro to Convert Multiple Excel Files to CSV Files

⧪ Step 4: Setting the Name of the Source Path and the Destination Path

Next, we’ll set the name of the source path and the destination path. In our data set, they are the same. That is the path of the folder selected by the user.

File_Path = File_Dialog.SelectedItems(1) & "\"
Destination_Path = File_Dialog.SelectedItems(1) & "\"

Setting File Path to Develop the Macro to Convert Multiple Excel Files to CSV Files

⧪ Step 5: Iterating through a Loop to Save All the Excel Files as CSV Files

Then we’ll iterate through a while-loop to save all the Excel files in the folder as CSV files.

File_Name = Dir(File_Path & "*.xls*")
Do While File_Name <> ""
    Set File = Workbooks.Open(Filename:=File_Path & File_Name)
    Destination_Name = Destination_Path & Left(File_Name, InStr(1, File_Name, ".") - 1) & ".csv"
    File.SaveAs Filename:=Destination_Name, FileFormat:=xlCSV
    File.Close savechanges:=False
    File_Name = Dir
Loop

⧪ Step 6: Closing the Process

Finally, we’ll close the process.

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

Closing the Process of Developing the Macro to Convert Multiple Excel Files to CSV Files

Read More: How to Convert Excel Files to CSV Automatically (3 Easy Methods)


Similar Readings


Developing the Macro to Convert Multiple Excel Files to CSV Files

We’ve seen the step-by-step analysis of the code to convert multiple Excel files to CSV files.

Therefore, the complete VBA code to develop the Macro will be:

⧭ VBA Code:

Sub Convert_Multiple_Excel_Files_to_CSV_Files()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)

File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Folder with the Excel Files"
If File_Dialog.Show <> -1 Then
    Exit Sub
End If

File_Path = File_Dialog.SelectedItems(1) & "\"
Destination_Path = File_Dialog.SelectedItems(1) & "\"

File_Name = Dir(File_Path & "*.xls*")
Do While File_Name <> ""
    Set File = Workbooks.Open(Filename:=File_Path & File_Name)
    Destination_Name = Destination_Path & Left(File_Name, InStr(1, File_Name, ".") - 1) & ".csv"
    File.SaveAs Filename:=Destination_Name, FileFormat:=xlCSV
    File.Close savechanges:=False
    File_Name = Dir
Loop

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

VBA Code to Develop a Macro to Convert Multiple Excel Files to CSV Files

⧭ Output: 

Run this code. A dialogue box will open that’ll ask you to select the folder with the Excel files.

Here I’ve selected the folder ExcelDemy.

Then click OK

Then wait for a few moments, and you’ll find a copy of CSV files of all the Excel files created in the same folder.

Read More: How to Convert Excel to CSV without Opening (4 Easy Methods)


Things to Remember

  • The code can convert not only .xlsx files but also all the files that have .xls in their extension (Like .xls, .xlsm, .xlsb, etc.)
  • If you want to save them as CSV files in a different folder other than the parent folder, use one more FileDialog object in the code to let the user select the destination folder.
  • While converting a large number of files, the code may take some time to finish running properly. So be patient and wait till the process ends successfully.

Conclusion

Therefore, this is the process to develop a Macro to convert multiple Excel files to CSV files. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo