Objective
To import multiple text files in separate excel files using VBA excel.
Approach
In this VBA Excel automation, we shall import the data from multiple text files and save each of them in separate workbook.For demonstration purpose we shall import 2 text files from input folder. Both the text files have tab delimited data. When we execute VBA code, 2 excel files will be saved in output folder. Both output files will have the same data as that of source text files.
Steps
- Initialize the path of source and target data folder
- Loop through each text file in source data folder
- Open the tab delimited text files in the excel workbook
- Save each of the opened workbook as excel file (xlsx)
- Close the excel file which has the data from text file
- Move on to next text file
Code
Option Explicit Sub ImportMultipleTextFile() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim InputTextFile As Variant Dim SourceDataFolder As String, OutputDataFolder As String SourceDataFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Source_Data" OutputDataFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Output_Data" '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 ActiveWorkbook.SaveAs Filename:=OutputDataFolder & "\" & Replace(ActiveWorkbook.Name, ".txt", ".xlsx"), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ActiveWorkbook.Close InputTextFile = Dir Wend End Sub
We had placed input text files in source_data folder, and when we execute VBA code output excel files will be stored in output_data folder.We can see the folder structure in screenshot below.
We can see in the image below that we had 2 text files [test_1.txt and test_2.txt] in the source folder. Both the text files contained ISD codes of 4 countries. We shall import both the files in excel.
Once we execute the VBA code, 2 excel files [test_1.xlsx and test_2.xlsx] will be created in output folder as seen below. Both the output excel files will have same data as that of source text files.
Notes
- We can open other type of files (e.g. csv or xlsm) as well by following this approach.
- We can also select input and output folder by using dialog box.
- We can also use macro recoding feature if we face any challenge due to version or file type.
Reference
https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.opentext
Post you may like
Import multiple csv file to single worksheet by vba excel – debugvba.com