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

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

1. Initialize the path of source and target data folder
2. Loop through each text file in source data folder
3. Open the tab delimited text files in the excel workbook
4. Save each of the opened workbook as excel file (xlsx)
5. Close the excel file which has the data from text file
6. 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

1. We can open other type of files (e.g. csv or xlsm) as well by following this approach.
2. We can also select input and output folder by using dialog box.
3. 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