How to import text files to Excel as separate sheets with original names in Excel?
Have you ever tried to import text files to Excel as separate sheets with the files’ original names in Excel? In this article, I introduce a VBA code to quickly import text files from one folder to Excel with the names.
Quickly split multiple worksheets into separate workbook in Excel
|In Microsoft Excel, you can save or split a worksheet from one workbook as a new Excel file by copying and pasting this worksheet into a new workbook. It seems troublesome, if you want to split each sheet / worksheet of a large workbook as separate Excel, txt, csv, pdf files. But with Kutools for Excel’s Split Workbook utility, you can quickly deal with it. Click for 60 days free trial!|
|Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.|
1. Enable Excel, press Alt + F11 keys to enable Microsoft Visual Basic for Applications window, then click Insert > Module.
2. Copy and paste below code to the Module script.
VBA: Import text files with names
Sub LoadPipeDelimitedFiles() 'UpdatebyExtendoffice20181010 Dim xStrPath As String Dim xFileDialog As FileDialog Dim xFile As String Dim xCount As Long Dim xWS As Worksheet On Error GoTo ErrHandler Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker) xFileDialog.AllowMultiSelect = False xFileDialog.Title = "Select a folder [Kutools for Excel]" If xFileDialog.Show = -1 Then xStrPath = xFileDialog.SelectedItems(1) End If If xStrPath = "" Then Exit Sub Application.ScreenUpdating = False xFile = Dir(xStrPath & "\*.txt") Do While xFile <> "" xCount = xCount + 1 Set xWS = Sheets(xCount) xWS.Select xWS.Name = "TEXT " & Left(xFile, Len(xFile) - 4) With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _ & xStrPath & "\" & xFile, Destination:=Range("A1")) .Name = "a" & xCount .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 xFile = Dir End With Loop Application.ScreenUpdating = True Exit Sub ErrHandler: MsgBox "no files txt", , "Kutools for Excel" End Sub
3. Press F5 to run the code, then select a folder which contains text files you want to import to Excel in the Select a folder dialog.
4. Click OK, the text files in the folder have been imported in Excel as single sheet with their own names.
Tabbed browsing & editing multiple Word documents/Excel workbooks as Firefox, Chrome, Internet Explore 10!
You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Word documents or Excel workbooks in one Word window or Excel window, and easily switch between them by clicking their tabs.