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)

26

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

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

MORE FROM ALLEN

Put Your Space Before or After?

When working with spacing between paragraphs, Word allows you to specify exactly how much space should be either before or ...

Discover More

Specifying a Backup Location

Backup files created by Word are stored in the same folder in which the document is located. If you want them stored in a ...

Discover More

Disabling Excel's Help System

The Help system built into Excel can be quite a lifesaver when you need to find that quick tidbit that is slipping your mind. ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

MORE EXCELTIPS (MENU)

Saving a Workbook with a Preview

When you save your workbooks, Excel can also save a preview image (thumbnail) that can be displayed in the Open dialog box. ...

Discover More

Creating a CSV File

Need to get your data into a format that can be easily read by other programs? Chances are good that a simple CSV file will ...

Discover More

Saving Worksheets in Lotus 1-2-3 Format

You can export an Excel worksheet in a variety of formats. One of those formats is Lotus 1-2-3, which is available as an ...

Discover More
Subscribe

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

View most recent newsletter.

Comments for this tip:

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. 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 7 + 8?

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

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

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?


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
Subscribe

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.

Links and Sharing