How to Convert Multiple Excel Files to CSV: 3 Suitable Ways

Method 1 – Embedding VBA to Convert Multiple Excel Files to CSV

Steps

• Go to the Developer tab, click Visual Basic.

• Click Insert > Module.

• In the Module window, enter the following code.

Sub convert_Excel_to_csv()

Dim Con_Mul_Excel As Workbook
Dim Con_Mul_Excel_ws As Worksheet
Dim Con_Mul_Excel_path As String
Dim Con_Mul_Excel_file As String
Dim Con_Mul_Excel_fd As FileDialog
Dim Con_Mul_Excel_sfd As FileDialog
Dim Con_Mul_Excel_spath As String
Dim Con_Mul_Excel_name As String

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

On Error Resume Next
Set Con_Mul_Excel_fd = Application.FileDialog(msoFileDialogFolderPicker)
Con_Mul_Excel_fd.AllowMultiSelect = False
Con_Mul_Excel_fd.Title = "Select the Folder for Your Excel Files"
If Con_Mul_Excel_fd.Show <> -1 Then Exit Sub
Con_Mul_Excel_path = Con_Mul_Excel_fd.SelectedItems(1) & "\"
Set Con_Mul_Excel_sfd = Application.FileDialog(msoFileDialogFolderPicker)
Con_Mul_Excel_sfd.AllowMultiSelect = False
Con_Mul_Excel_sfd.Title = "Select Destination Folder"

If Con_Mul_Excel_sfd.Show <> -1 Then Exit Sub
Con_Mul_Excel_spath = Con_Mul_Excel_sfd.SelectedItems(1) & "\"
Con_Mul_Excel_file = Dir(Con_Mul_Excel_path & "*.xls*")

Do While Con_Mul_Excel_file <> ""
Set Con_Mul_Excel = Workbooks.Open(Filename:=Con_Mul_Excel_path & Con_Mul_Excel_file)
Con_Mul_Excel_name = Con_Mul_Excel_spath & Left(Con_Mul_Excel_file, InStr(1, Con_Mul_Excel_file, ".") - 1) & ".csv"
Con_Mul_Excel.SaveAs Filename:=Con_Mul_Excel_name, FileFormat:=xlCSV
Con_Mul_Excel.Close savechanges:=False
Con_Mul_Excel_file = Dir
Loop

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

End Sub

• Close the window.
• Go to View tab > Macros(Double click).

• After clicking View Macros, select the macros that you created just now. The name here is convert_Excel-to CSV. Click Run.

• After clicking Run, a new browser window will open; select the folder where your multiple Excel files are stored, select the folder and click OK.

• Another window will open asking the location of the CSV file after converted from Excel files. Select the destination folder and click OK.

• The circulating loading icon. Check the destination folder for the converted CSV files.
• In the destination folder, all three converted CSV files are the file extension shows as CSV.

• Click on Dataset_1 for inspection.
• After clicking the file, you will notice that it has opened and is showing as a CSV file rather than an Excel file.
• The conversion is successful.

• Click on Dataset_2 for inspection.
• After clicking the file, you will notice that it has opened and is showing as a CSV file rather than an Excel file.
• That affirms that the conversion is successful.

• Click on Dataset_3 for inspection.
• After clicking the file, you will notice that it has opened and is showing as a CSV file rather than an Excel file.
• That affirms that the conversion is successful.

Method 2 – Combine Multiple Excel Files and Convert Them to CSV

Steps

• Go to the Developer tab, then click Visual Basic.

• Click Insert > Module.

• In the Module window, enter the following code.

Sub Combine_Excel_Files()

Dim fnList, fnCurFile As Variant
Dim cFiles, cSheets As Integer
Dim wkCurSheet As Worksheet

Dim wbCurBook, wbSrcBook As Workbook

fnList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
If (vbBoolean <> VarType(fnList)) Then
If (UBound(fnList) > 0) Then
cFiles = 0
cSheets = 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wbCurBook = ActiveWorkbook
For Each fnCurFile In fnList
cFiles = cFiles + 1
Set wbSrcBook = Workbooks.Open(Filename:=fnCurFile)
For Each wkCurSheet In wbSrcBook.Sheets
cSheets = cSheets + 1
wkCurSheet.Copy after:=wbCurBook.Sheets(wbCurBook.Sheets.Count)
Next
wbSrcBook.Close SaveChanges:=False
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Worked on " & cFiles & " files" & vbCrLf & "Joined " & cSheets & " worksheets", Title:="Merging Multiple Excel Files"
End If
Else
MsgBox "No File is Selected", Title:="Merging Multiple Excel files"
End If

End Sub

• Close the window.
• Go to View tab > Macros(Double click).

• After clicking View Macros, select the macros that you created just now. The name here is Combine_Excel_Files. Click Run.

• A new file browser window will open, and from that window, you need to select the files you want to merge into a single Excel file.
• Select the files and click Open.

• After clicking Open, you will notice that there are loading screens one by one for each of the sheets. After a few moments, the below window will spawn with the following message.

• You will see three different Excel workbooks are now loaded into the active Excel Workbook as individual worksheets.

• Go to the Developer tab, and click Visual Basic.

• Click Insert > Module.

• In the Module window, enter the following code.

Sub Excel_TO_CSV()

Dim wrsh As Worksheet
Dim excsv As String

For Each wrsh In Application.ActiveWorkbook.Worksheets
wrsh.Copy
excsv = CurDir & "\" & wrsh.Name & ".csv"
Application.ActiveWorkbook.SaveAs Filename:=excsv, _
FileFormat:=xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next

End Sub

• Close the window.
• Go to View tab > Macros(Double Click).

• After clicking View Macros, select the macros that you created just now. The name here is Excel_TO_CSV. Then click Run.

• After clicking run, the worksheet will have moments of the loading phase.
• Then, you need the documents folder on your computer. The converted CSV files are in this directory.
• You will notice that the file extensions of these files are showing in CSV format.

• Open Dataset_1, Then you will notice that the file is In CSV format and is converted from the Excel worksheet.

Method 3 – Utilizing Online Conversion Tool

Steps

• Go to XLSX (EXCEL) to CSV (Online & Free) — Convertio. This is a very good online converter to convert multiple Excel sheets To CSV format.
• Select the input format and the output format of the files. The input format will be XLSX, select the output format CSV.
• On the website click the Choose Files. 

• A file browse menu will open, from that menu select the files that need to be converted and click Open. In this case, we selected Dataset_1, Datset_2, Dataset_3.

• All three of them will now be uploaded to the website, and their destination format will show CSV Click Convert after this.

• After clicking Convert, the conversion process will start.
• Click Download on the right side of each file to download the converted files.

• Open the download folder and notice that all of the Excel files are now converted to CSV format.

• Open Dataset_1 for inspection.
• Notice the file is now open as a CSV file. That means the conversion is successful.

Download Practice Workbook

Related Articles

• How to Create CSV File from Excel
• How to Convert Excel File to Text File with Comma Delimited
• Convert Excel to Comma Delimited CSV File
• How to Make a CSV File in Excel for Contacts
• How to Convert Excel Files to CSV Automatically
• How to Convert Excel to CSV without Opening
• [Fixed!] Excel Not Saving CSV with Commas

<< Go Back to Export Excel to CSV | Export Data from Excel | Learn Excel