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.
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)
If filePath = "" Then Exit Sub
Application.ScreenUpdating = False
openFile = Dir(filePath &amp; "\*.txt")
Do While openFile &lt;&gt; ""
sheetNum = sheetNum + 1
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
&amp; filePath &amp; "\" &amp; openFile, Destination:=Range("A1"))
.Name = "importFile" &amp; 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
openFile = Dir
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