Import multiple text files on separate worksheets tabs using vba excel -debugvba.com

Import multiple text file in separate worksheet

Objective

To import multiple text files on separate worksheets using VBA Excel

Approach

In this VBA Excel automation, we shall import data from multiple text file and save them in separate individual worksheets of single excel file. This consolidated excel file will be saved in output folder.To import the data from text file to excel file, data should have delimiter in the source data, else data will not be imported correctly from text file. We have showcased the folder structure below.

Steps

  1. Initialize the path of input and output folder in the 2 variables
  2. Add a new workbook by VBA code, all the data from multiple text files will be imported in this workbook
  3. Loop through each text file in source data folder
  4. Copy the range from opened text file, we have taken the range A2:B10 for demonstration purpose
  5. Add a new worksheet in each iteration, each worksheet will have data of a separate workbook
  6. Paste the copied data in last blank row in newly added worksheet
  7. Change the name of the worksheet, rename it as name of opened text file
  8. Close the input text file
  9. Move to next file in source folder
  10. 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, LastRow As Integer

SourceDataFolder = "C:\Users\shobhitsaxena\Desktop\New\Source_Data"
OutputDataFolder = "C:\Users\shobhitsaxena\Desktop\New\output_data"

'Add consolidated workbook
Set wb = Workbooks.Add

'Loop through each text file in source folder
InputTextFile = Dir(SourceDataFolder & "\*.txt")
While InputTextFile <> ""
Workbooks.OpenText Filename:=SourceDataFolder & "\" & InputTextFile, 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(InputTextFile, ".txt", "")
ActiveSheet.Paste
Range("A1").Select

'Close the opened input file
Workbooks(InputTextFile).Close
InputTextFile = 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; one is for output file (output_data) and the another one for input text files (source_data).

Import multiple text file in separate worksheet

 


Folder source_data contains 2 text files, which are test_1.txt and test_2.txt. Both the text files have 4 records each. Data is tab delimited in both the files.

 

Import multiple text file in separate worksheet

 

Once the VBA code is executed, one output excel file will be created in output_data folder. It will have data in separate worksheets.

Import multiple text file in separate worksheet
Output excel workbook will have separate worksheet for each text file. We can see in the image below that we have 2 worksheets for test_1 and test_2 text files.

Import multiple text file in separate worksheet

 

Notes

  1. We can also select the source and destination folder by dialog box to make code more dynamic.
  2. We can also import file which has delimiter other than tab or comma, for other options we can go through OpenText method of VBA.
  3. We can import comma delimited; 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