How to Import Multiple Text Files to Multiple Sheets in Excel

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