Import multiple csv file to separate excel workbook by vba excel.
In this VBA Excel automation, we shall import the data from multiple csv files and save each of them as a separate workbook.For demonstration purpose we shall import 2 csv files from input folder. When we execute VBA code, 2 excel files will be saved in output folder. Both output files will have the same data as that of input csv files.
- Initialize the path of input and output data folder
- Loop through each csv file in input data folder
- Open the csv file in the excel workbook
- Save each of the opened csv as excel file (xlsx)
- Close the excel file which has the data from csv file
- Move on to next csv file
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\macro\Input_Data" OutputFolder = "C:\Users\yourusername\Desktop\macro\Output_Data" 'Loop through each csv file in source folder InputCsvFile = Dir(InputFolder & "\*.csv") 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, ".csv", ".xlsx"), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ActiveWorkbook.Close InputCsvFile = Dir Wend End Sub
We can see in the image below that we had 2 csv files [test_1.csv and test_2.csv] in the input folder. Both the csv files contained ISD codes of 4 countries. We shall import both the files in excel.
Once we execute the VBA code, 2 output excel files [test_1.xlsx and test_2.xlsx] will be created in output folder as seen below. Both the output excel files will have same data as that of input csv files.
- We can open any other type of files (e.g. txt or xlsm) as well, by following this approach.
- We can also select input and output folder by dialog box.
- We can also use macro recording feature if we face any challenge due to version or file type.
Post you may like