To Import multiple csv file to separate worksheet by vba excel
In this VBA Excel automation, we shall import data from multiple csv file and save them in a separate worksheet in single consolidated file.We can see the folder structure below to understand how this code works.
- Declare and 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
- 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
- Add a worksheet in newly added workbook
- Paste the copied data in newly added worksheet
- Close the input csv file
- Move to next csv file in input folder and repeat the same process
- Once all the csv files are imported, save and close the workbook
'Option Explicit Sub ImportMultipleCsvFile() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim InputCsvFile As Variant Dim SourceDataFolder As String, OutputDataFolder As String Dim wb As Workbook, LastRow As Integer SourceDataFolder = "C:\Users\username\Desktop\macro\Input_Data" OutputDataFolder = "C:\Users\username\Desktop\macro\Output_Data" 'Add consolidated workbook Set wb = Workbooks.Add 'Loop through each csv file in source folder InputCsvFile = Dir(SourceDataFolder & "\*.csv") While InputCsvFile <> "" Workbooks.OpenText Filename:=SourceDataFolder & "\" & InputCsvFile, DataType:=xlDelimited, Space:=True 'Save each output file in output folder Range("A1:B10").Copy 'Paste it to newly added workbook wb.Activate Worksheets.Add ActiveSheet.Name = Replace(InputCsvFile, ".csv", "") ActiveSheet.Paste Range("A1").Select 'Close the opened input file Workbooks(InputCsvFile).Close InputCsvFile = Dir Wend 'Save the consolidated sheet wb.SaveAs Filename:=OutputDataFolder & "\" & "Consolidated_File", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False wb.Close End Sub
We have used 2 folders, first one for output files (output_data) and second 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.
Above shown consolidated output excel file will have 2 tabs, each tab will have data from source csv file.
- 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