How to Import multiple text files in single worksheet using vba excel -debugvba.com

Import multiple text file in single worksheet

Objective

To import multiple text files in single worksheet using VBA Excel.

Approach

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.

Steps

  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

Code

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).

 

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


Notes

  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”

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.opentext
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function

Post you may like

Import multiple text files in separate excel files using vba excel – debugvba.com