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.
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 &amp; "\*.txt") Do While openFile &lt;&gt; "" sheetNum = sheetNum + 1 Sheets(sheetNum).Select 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 .Refresh BackgroundQuery:=False openFile = Dir End With Loop Exit Sub End Sub
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