To import multiple text files on separate worksheets using VBA Excel
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.
- Initialize the path of input and output folder in the 2 variables
- Add a new workbook by VBA code, all the data from multiple text files will be imported in this workbook
- 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
- Add a new worksheet in each iteration, each worksheet will have data of a separate workbook
- Paste the copied data in last blank row in newly added worksheet
- Change the name of the worksheet, rename it as name of opened text file
- 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, 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).
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.
Once the VBA code is executed, one output excel file will be created in output_data folder. It will have data in separate worksheets.
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.
- We can also select the source and destination folder by dialog box to make code more dynamic.
- We can also import file which has delimiter other than tab or comma, for other options we can go through OpenText method of VBA.
- We can import comma delimited; we can use “comma: =True”