This post will guide you how to import multiple .txt files into separate worksheets in the opened workbook in Excel. How do I quickly import multiple text files from a local disk into each worksheets with VBA Macro in Excel 2013/2016.

Import Multiple Text Files into Separate Worksheets

---

Assuming that you have opened one workbook, and you want to import all text files under d:/text directory into your current workbook, and each text file should import a newly worksheet. The below steps will show you how to use an Excel VBA macro to accomplish it:

Step1: you need to make sure that how many text files you want to import, for example, you want to import 2 text files from a foler called text, then You need to make sure that there are at least two worksheets so that it can import those two text files into those two worksheets.

Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step3: then the “Visual Basic Editor” window will appear. Step4: click “Insert” ->”Module” to create a new module.

Step5: paste the below VBA code into the code window. Then clicking “Save” button.

[vb]
Sub ImportTextFilesIntoSheets()
Dim filePath As String
Dim openFileDialog As FileDialog
Dim openFile As String
Dim sheetNum As Long
Set openFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
openFileDialog.AllowMultiSelect = False
openFileDialog.Title = "select one directory that contain text files:"
If openFileDialog.Show = -1 Then
filePath = openFileDialog.SelectedItems(1)
End If
If filePath = "" Then Exit Sub
Application.ScreenUpdating = False
openFile = Dir(filePath & "\*.txt")
Do While openFile <> ""
sheetNum = sheetNum + 1
Sheets(sheetNum).Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
& filePath & "\" & openFile, Destination:=Range("A1"))
.Name = "importFile" & sheetNum
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
openFile = Dir
End With
Loop

Exit Sub
End Sub

[/vb]

Step6: back to the current worksheet, then run the above excel macro. Click Run button.

Step7: select one directory that contain text files. click Ok button.

Step8: let’s see the last result

: