Import multiple text files as separate worksheets in Excel

Tutorial

If there are large number of text files that you need to import as a separate worksheet, follow these guidelines. Note that the file name of the text file will be used to label the worksheet (tab), without the .txt extension.

Before proceeding, check if you have enabled the macros, i.e., if you don't see DEVELOPER tab in you empty spreadsheet, click on FILE, OPTIONS and Customize Ribbon. You should see a check box on the right hand side, for the DEVELOPER tab, check it and click OK.

Click on DEVELOPER and then Macros, type in some name (eg. import_text), click create.

Paste the below code on the popped window:

  1. Sub CombineTextFiles()
  2. Dim FilesToOpen
  3. Dim x As Integer
  4. Dim wkbAll As Workbook
  5. Dim wkbTemp As Workbook
  6. Dim sDelimiter As String
  7.  
  8. On Error GoTo ErrHandler
  9. Application.ScreenUpdating = False
  10.  
  11. sDelimiter = "|"
  12.  
  13. FilesToOpen = Application.GetOpenFilename _
  14. (FileFilter:="Text Files (*.txt), *.txt", _
  15. MultiSelect:=True, Title:="Text Files to Open")
  16.  
  17. If TypeName(FilesToOpen) = "Boolean" Then
  18. MsgBox "No Files were selected"
  19. GoTo ExitHandler
  20. End If
  21.  
  22. x = 1
  23. Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
  24. wkbTemp.Sheets(1).Copy
  25. Set wkbAll = ActiveWorkbook
  26. wkbTemp.Close (False)
  27. wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
  28. Destination:=Range("A1"), DataType:=xlDelimited, _
  29. TextQualifier:=xlDoubleQuote, _
  30. ConsecutiveDelimiter:=False, _
  31. Tab:=False, Semicolon:=False, _
  32. Comma:=False, Space:=False, _
  33. Other:=True, OtherChar:="|"
  34. x = x + 1
  35.  
  36. While x <= UBound(FilesToOpen)
  37. Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
  38. With wkbAll
  39. wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
  40. .Worksheets(x).Columns("A:A").TextToColumns _
  41. Destination:=Range("A1"), DataType:=xlDelimited, _
  42. TextQualifier:=xlDoubleQuote, _
  43. ConsecutiveDelimiter:=False, _
  44. Tab:=False, Semicolon:=False, _
  45. Comma:=False, Space:=False, _
  46. Other:=True, OtherChar:=sDelimiter
  47. End With
  48. x = x + 1
  49. Wend
  50.  
  51. ExitHandler:
  52. Application.ScreenUpdating = True
  53. Set wkbAll = Nothing
  54. Set wkbTemp = Nothing
  55. Exit Sub
  56.  
  57. ErrHandler:
  58. MsgBox Err.Description
  59. Resume ExitHandler
  60. End Sub

Now you are all set to import files. Click on Macros again, and then Run. A window should pop up asking location of the text files. Navigate to the files location and select all text files. Click OK. All text files will be imported to a new spreadsheet. It might take a while to complete, if there are large number of files, so be patient.