How to Import multiple text files in single worksheet using vba excel

Import multiple text file in single worksheet


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.


  1. Initialize the path of input and output folder in the 2 variables
  2. Add a new workbook, all the data from multiple text files will be imported in this workbook
  3. Add the column header for 2 columns in cell A1 and B1
  4. Loop through each text file in source data folder
  5. Copy the range from opened text file, we have taken the range A2:B10 for demonstration purpose
  6. Paste the copied data in last blank row in a newly added workbook
  7. Close the input text file
  8. Move to next file in source folder
  9. 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

'Paste it to newly added workbook
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & LastRow + 1).Select

'Close the opened input file
InputTextFile = Dir

'Save the consolidated sheet
wb.SaveAs Filename:=OutputDataFolder & "\" & "Consolidated_File", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub



We have used 2 folders, one for output files (output_data) and another one for input files (source_data).


Import multiple text file in single worksheet

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.


Import multiple text file in single worksheet


When we execute the VBA code, 1 consolidated output excel file will be created in output_data folder.


Import multiple text file in single worksheet

Consolidated output excel file shown above will have total 8 records; as it imported 4 records from each of the input text files.


Import multiple text file in single worksheet


  1. We can make the code more dynamic by selecting the source and destination by dialog box
  2. We can also import file which has any delimiter other than “Tab”, for other options we can go through OpenText method of VBA.
  3. To import comma delimited data; we can use “comma:=True”



Post you may like

Import multiple text files in separate excel files using vba excel –