How to Import Multiple Text Files into Seperate Worksheets in Microsoft Excel

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. import multiple text file into worksheet1

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

Get the position of the nth using excel vba1

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

export each sheet to csv2

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

import multiple text file into worksheet2

[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.

import multiple text file into worksheet3

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

import multiple text file into worksheet6

Step8: let’s see the last result

: import multiple text file into worksheet5

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar