Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Importing Multiple Files to a Single Workbook.
Let's say that you have a folder on your hard drive that contains thirty text files, and you want to import all of them to an Excel workbook. You want each text file to end up on its own worksheet in the workbook, so that you will have a total of thirty worksheets.
One way to do this is to manually add the desired worksheets, and then individually import each of the text files. This, as you can imagine, would quickly get tedious. A much better solution is to use a macro to do the importing, such as the following one.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
Sub CombineTextFiles()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String
On Error GoTo ErrHandler
Application.ScreenUpdating = False
sDelimiter = "|"
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")
If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If
x = 1
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"
x = x + 1
While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=sDelimiter
End With
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
This macro allows you to select which files you want to import, and then it places the data from those files onto the separate worksheets in the workbook. The macro assumes that the data being imported uses the pipe character (|) as a delimiter between fields.
If you know that the files to be imported are always in the specific folder, and that you want to import all the files in that folder, then you can simplify the macro a bit. The following example assumes that the files are in the folder c:\temp\load_excel, but you could change that folder name by making a simple change to fpath variable in the macro code.
Sub LoadPipeDelimitedFiles()
Dim idx As Integer
Dim fpath As String
Dim fname As String
idx = 0
fpath = "c:\temp\load_excel\"
fname = Dir(fpath & "*.txt")
While (Len(fname) > 0)
idx = idx + 1
Sheets("Sheet" & idx).Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
& fpath & fname, Destination:=Range("A1"))
.Name = "a" & idx
.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
fname = Dir
End With
Wend
End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3148) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Importing Multiple Files to a Single Workbook.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When you save a workbook to disk, you may want to automatically save a duplicate workbook in a separate location. This ...
Discover MoreIf you have a full path designation for the location of a file on your hard drive, you may want a way for Excel to pull ...
Discover MoreWhen storing your Excel workbook, you need to specify a file name to be used for the workbook. Take a moment to consider ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-12-19 09:21:54
Hugh
Run-time error '438':
Object doesn't support this property or method
2016-11-28 23:17:24
Cade Huff
huh,
This is a bit of a time lag, but...
you will want to move the "Set wkbAll= ActiveWorkbook" to just before the x=1 and remove the first x=x+1. This will open everything in the workbook that you ran it from and it will guarantee that the first file is not lost when importing the files.
I hope that helps out.
2016-11-22 12:01:05
Sean
what would I change to get it to open them with sheet titles Sheet 1, sheet 2 etc and not titled as the name?
2016-03-30 11:40:20
huh
Please paste the code so that the Excel files that I upload will be pasted into a previously created/existing workbook rather than a newly opened Excel workbook.
Thank you very much!
2016-03-29 13:14:56
vivek pare
i want to know how to import 100 or 200 text files having space as delimiter insted of pipe"|"
can you send me the macro or suggest some modifications
with regards
vivek pare
2016-02-21 17:54:26
Andrei
Hello.
The trick is great but I have two questions :
- How can I keep the name of the sheets as default : Sheet1, Sheet2, etc ?
- And how can I add the txt files after the current sheets not in a new workbook
Thank you very much !
2016-02-09 16:07:54
Chux
I changed .txt to .log and it will only pull the 1st file I select. I also get the error "object doesn't support this property or method". Please help.
2016-01-13 09:22:12
Ravi Kumar
Hello,
I am happy to find this tip. Thank you so much. However I have a request.
This macro is opening a new workbook instead it should be current workbook with all existing sheet names as text file names. Can you please provide the code for current workbook and overwrite the sheets with data and sheet names in the current workbook. Thank you so much.
2015-07-24 11:22:54
Zaki
God bless you. Awesome solution
2015-07-15 23:28:15
Ann
Hi, I am doing the exact same thing except that I'm importing excel files instead of text files.
I used the first macro code and changed the txt to xlsx but it doesn't work. The error that came out is "object doesn't support this property or method".
May I know how to import excel files instead?
2015-04-13 19:15:42
Softie
Thank you for the code.
However, this code wipes out all previous worksheets. How do i prevent this from wiping out my summary sheets?
TIA
softie
2015-04-09 06:35:11
Michael (Micky) Avidan
@Chapers,
Try: Sheets.Add Before:=ActiveSheet
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
2015-04-08 09:36:01
Chapers
I am using the second lot of coding and works perfect thanks. I would like to add to the begin, insert new worksheet before active sheet (whatever that may be, will differ with files) could you advise, keep getting error 9
2014-06-20 01:01:48
Sid
Love it!
Does anyone know of how I can modify the code so it imports all the text files as text (so it keeps leading 0's etc)
Help would be appreciated.
2014-06-17 08:15:09
Vibhu Kunwar Chauhan
Very Helpful Code For Me.
Thanks Allen.
2014-06-10 14:59:02
Cris
This script aids us with automating the data import from 3 separate files into one excel file with 3 different sheets that the business experts will use for data cleanup.
Superb work, thank you!
2014-05-27 14:41:23
Bernard
Hi, excellent tip! but I would like to have all the files on the same sheet, and I dont know what should I change on the code.
Thanks!
2013-10-13 22:36:52
This is the perfect macro for my needs however it only adds data from files for as many sheets are in the workbook - how can i ammend the code so that it creates a new sheet before it imports each separate text file?
2013-09-13 12:34:53
Wojciech
works fine!
Very helpful and handy, however after importing 380 files procedure crashes completely leaving an error message and an open excel lone sheet window + workbook with already pasted sheets - fully functionable though.
As I needed to import teen hundreds of records - M$ Excel proved its weakness comparing to bash data processing scripts. Nevertheless - thanks a lot Allen, your skills helped me a lot!
2013-09-05 13:20:20
Mike
heres the issue you code works great for what I am doing so thank you for that. The issue im having is I have placed your code into a button on a form. your code opens a brand new workbook is there a way to tell the program which named workbook to import the data to?
2013-08-02 11:04:01
Crystal
This was exactly what I was looking for -- what a time saver, thanks!
2013-07-18 11:01:28
keiko
This works beautifully. Is there any way to replace name of the sheets with name of the text files?
2013-04-19 15:24:59
Raghu
It worked. Thanks
2013-02-14 15:47:39
Nathan
I'm looking to perform something very similar.
I have .html files in a folder on my desktop. Each html file would be imported into it's own separate worksheet.
I attempted to modify the code you provided, but my skills are lackluster.
Any direction would be much appreciated.
2013-01-31 02:40:21
carlos
It works "KIND OF" perfectly !
my delimiter is ";"
I can import all files easily, but the last line is not separated by columns. neither the first file. So i have to "CHECK" that pages are imported correctly, ..instead of doing it my self
Any idea why ?
2012-09-05 17:08:16
Tim
I have a similar challenge but slightly different.
I have one directory with several hundred spreadsheets. I would like to import each of these into a single spreadsheet.
Advise?
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Comments