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
- Declare and initialize the variables for input and output folder
- Loop through each excel (.xlsx) file in the source folder
- Open each excel file in source folder
- Save the newly added workbook as csv file
- Close the csv file
- 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