To import multiple text files in single worksheet using VBA Excel.
In this VBA Excel automation, we shall import data from multiple text file and save them in a single excel file. Data from all the files will be saved in single worksheet.To import the data from text file to excel file, it is important to have delimiter in the source data, otherwise data will not be imported correctly. We can see the folder structure below.
- Initialize the path of input and output folder in the 2 variables
- Add a new workbook, all the data from multiple text files will be imported in this workbook
- Add the column header for 2 columns in cell A1 and B1
- Loop through each text file in source data folder
- Copy the range from opened text 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 text file
- Move to next file in source folder
- Once all the files are imported, save and close the workbook
Option Explicit Sub ImportMultipleTextFile() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim InputTextFile As Variant Dim SourceDataFolder As String, OutputDataFolder As String Dim wb As Workbook Dim LastRow As Integer SourceDataFolder = "C:\Users\yourusernme\Desktop\Source_Data" OutputDataFolder = "C:\Users\yourusernme\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 InputTextFile = Dir(SourceDataFolder & "\*.txt") While InputTextFile <> "" Workbooks.OpenText Filename:=SourceDataFolder & "\" & InputTextFile, DataType:=xlDelimited, Tab:=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(InputTextFile).Close InputTextFile = Dir Wend 'Save the consolidated sheet wb.Sheets("sheet1").Range("A1").Select wb.SaveAs Filename:=OutputDataFolder & "\" & "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 (source_data).
Folder source_data has 2 input text files, [test_1.txt and test_2.txt]. Both the text files have 4 records. Data is tab delimited in both files.
When we execute the VBA code, 1 consolidated output excel file will be created in output_data folder.
Consolidated output excel file shown above 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
- We can also import file which has any delimiter other than “Tab”, for other options we can go through OpenText method of VBA.
- To import comma delimited data; we can use “comma:=True”
Post you may like