Convert multiple excel files into csv files in VBA Excel

Objective

To convert multiple excel files into csv files using VBA Excel.

Approach

In this VBA Excel automation,we have 2 excel files in input data folder.Then we open first excel file and save it as csv file.After that we move to next file.

Steps

1. Declare and initialize the variables for input and output folder
2. Loop through each excel (.xlsx) file in the source folder
3. Open each excel file in source folder
4. Save the newly added workbook as csv file
5. Close the csv file
6. Move on to next excel file

 

Code

 

Option Explicit


Sub ImportMultipleCsvFile()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim InputCsvFile As Variant
Dim InputFolder As String, OutputFolder As String


InputFolder = "C:\Users\yourusername\Desktop\Input_Data"
OutputFolder = "C:\Users\yourusername\Desktop\Output_Data"


'Loop through each csv file in source folder

InputCsvFile = Dir(InputFolder & "\*.xlsx")
While InputCsvFile <> ""
Workbooks.OpenText Filename:=InputFolder & "\" & InputCsvFile, DataType:=xlDelimited, Comma:=True

'Save each output file in output folder
ActiveWorkbook.SaveAs Filename:=OutputFolder & "\" & Replace(ActiveWorkbook.Name, ".xlsx", ".csv"), FileFormat:=xlCSV, CreateBackup:=False


ActiveWorkbook.Close
InputCsvFile = Dir
Wend


End Sub

 

We had 2 folders, in the input_data folder we had 2 excel files. We shall convert these excel files to csv files.

 

Input data folder had 2 excel files as shown below.

 

 

Once the VBA code is executed, 2 csv files will be generated in the output folder as shown below.

 

Post you may like

Import multiple csv file to separate excel workbook by vba excel