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.

Importing Multiple Files to a Single Workbook

by Allen Wyatt
(last updated December 29, 2014)


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.

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))
    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

    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub

    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
End Sub

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...


Positioning Line Numbers

Line numbers can be added to a document, and is typically done in legal documents. The line numbers appear to the left of ...

Discover More

Creating a Plus/Minus Button

Want a quick way to convert positive values to negative and vice versa? You can create your own plus/minus button by using a ...

Discover More

Changing Directories in a Macro

Need to specify which directory on your hard drive should be used by a macro? It's easy to do using the ChDir command.

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Correctly Saving Delimited Files

Delimited files are often created through Excel so that your data can be exported to other programs. If the delimited data ...

Discover More

Determining the Length of a Text File

When processing plain text files in a macro, it is often helpful to know how much data the file contains. The normal way to ...

Discover More

Opening Non-Excel Files

Not all data is created in Excel. Indeed, you may have data in files created by many other types of programs. You might be ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 - 2?

2016-12-19 09:21:54


Run-time error '438':

Object doesn't support this property or method

2016-11-28 23:17:24

Cade Huff


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


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


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


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


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


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


God bless you. Awesome solution

2015-07-15 23:28:15


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


Thank you for the code.
However, this code wipes out all previous worksheets. How do i prevent this from wiping out my summary sheets?

2015-04-09 06:35:11

Michael (Micky) Avidan

Try: Sheets.Add Before:=ActiveSheet
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)

2015-04-08 09:36:01


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


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


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


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.


2013-10-13 22:36:52

Tim Searle

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


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


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


This was exactly what I was looking for -- what a time saver, thanks!

2013-07-18 11:01:28


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


It worked. Thanks

2013-02-14 15:47:39


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


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


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.


This Site

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.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.