Import multiple csv file to separate excel workbook by vba excel – debugvba.com

Import multiple csv to excel vba

Objective

Import multiple csv file to separate excel workbook by vba excel.

Approach

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.

Steps

  1. Initialize the path of input and output data folder
  2. Loop through each csv file in input data folder
  3. Open the csv file in the excel workbook
  4. Save each of the opened csv as excel file (xlsx)
  5. Close the excel file which has the data from csv file
  6. Move on to next csv 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\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.

 

Import multiple csv to excel vba

 

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.

 

Import multiple csv to excel vba

Notes

  1. We can open any other type of files (e.g. txt or xlsm) as well, by following this approach.
  2. We can also select input and output folder by dialog box.
  3. We can also use macro recording feature if we face any challenge due to version or file type.

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.opentext

Post you may like

Import multiple csv file to single worksheet by vba excel