To import multiple csv file to single worksheet by vba excel.
In this VBA Excel automation, we shall import data from multiple csv file and save them in a single excel file. Data from all the csv files will be saved in single worksheet.We can see the folder structure below to understand how this code works.
- Initialize the path of input and output folder in the 2 variables
- Add a new workbook, all the data from multiple csv files will be imported in this workbook
- Add the column header for 2 columns in cell A1 and B1
- Loop through each csv file in source data folder
- Copy the range from opened csv file, we have taken the range A2:B10 for demonstration purpose
- Paste the copied data in last blank row in a newly added workbook
- Close the input csv file
- Move to next csv file in input folder
- Once all the csv files are imported, save and close the workbook
Option Explicit Sub ImportMultipleCsvFiles() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim InputCsvFile As Variant Dim InputFolder As String, OutputFolder As String Dim wb As Workbook Dim LastRow As Integer InputFolder = "C:\Users\username\Desktop\input_Data" OutputFolder = "C:\Users\username\Desktop\Output_Data" 'Add consolidated workbook, data from text files will be pasted in it Set wb = Workbooks.Add Range("A1").Value = "Country" Range("B1").Value = "ISD_Code" 'Loop through each text 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 Range("A2:B10").Select Selection.Copy 'Paste it to newly added workbook wb.Activate LastRow = Cells(Rows.Count, 1).End(xlUp).Row Range("A" & LastRow + 1).Select ActiveSheet.Paste 'Close the opened input file Workbooks(InputCsvFile).Close InputCsvFile = Dir Wend 'Save the consolidated sheet wb.Sheets("sheet1").Range("A1").Select wb.SaveAs Filename:=OutputFolder & "\" & "Consolidated_File", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False wb.Close End Sub
We have used 2 folders, one for output files (output_data) and another one for input files (input_data).
Folder input_data has 2 input csv files, [test_1.csv and test_2.csv]. Both the csv files have 4 records.
When we execute the VBA code, 1 consolidated output excel file will be created in output_data folder.
A consolidated output excel file will be created which will have total 8 records; as it imported 4 records from each of the input text files.
- We can make the code more dynamic by selecting the source and destination by dialog box
- To import comma delimited data; we use “comma:=True”
Post you may like