This post will introduce you how to import multiple text files into multiple sheets in Excel. You can quickly and easily import multiple text files into separate sheets within a single workbook using VBA code. Let’s get started with the step-by-step instructions for importing multiple text files using VBA.
Import Multiple Text Files to Multiple Sheets with VBA Code
If you have a large number of text files that you need to import into Excel, it can be a time-consuming task to import them one by one. You can achieve it by using VBA code, just do the following steps:
To import multiple text files into multiple sheets with VBA code in Excel, you can follow these steps:
Step1: Open a new workbook in Excel and press ALT + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, insert a new module by clicking Insert > Module.

Step3: Copy and paste the following VBA code into the new module:

Sub ImportTextFiles_excelgeek()
Dim FileNames As Variant
Dim i As Integer
FileNames = Application.GetOpenFilename(filefilter:="Text Files (*.txt),*.txt", MultiSelect:=True)
If Not IsArray(FileNames) Then
Exit Sub
End If
For i = LBound(FileNames) To UBound(FileNames)
With ActiveWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
.Name = Left(Right(FileNames(i), Len(FileNames(i)) - InStrRev(FileNames(i), "\")), Len(Right(FileNames(i), Len(FileNames(i)) - InStrRev(FileNames(i), "\"))) - 4)
.QueryTables.Add Connection:="TEXT;" & FileNames(i), Destination:=.Range("A1")
.QueryTables(1).TextFilePlatform = xlWindows
.QueryTables(1).TextFileStartRow = 1
.QueryTables(1).TextFileParseType = xlDelimited
.QueryTables(1).TextFileTextQualifier = xlTextQualifierDoubleQuote
.QueryTables(1).TextFileConsecutiveDelimiter = False
.QueryTables(1).TextFileTabDelimiter = True
.QueryTables(1).TextFileSemicolonDelimiter = False
.QueryTables(1).TextFileCommaDelimiter = False
.QueryTables(1).TextFileSpaceDelimiter = False
.QueryTables(1).TextFileOtherDelimiter = ""
.QueryTables(1).Refresh BackgroundQuery:=False
End With
Next i
End Sub
Read More: How to Use QueryTables in Excel VBA
Step4: Save the module and return to the Excel worksheet.
Step5: Press ALT + F8 to open the Macro dialog box and run the ” ImportTextFiles_excelgeek ” macro.

Step6: In the Open dialog box that appears, select the text files you want to import and click the “Open” button.

Step7: The VBA code will create a new sheet for each text file and import the contents of each file into its corresponding sheet.

This VBA code makes it easy to import multiple text files into multiple sheets in Excel with just a few clicks.
Read More: Import or Merge Multiple CSV Files into one Excel Worksheet using Power Query