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

Turning Off HTML Conversions

Don't want Word to load up your HTML documents as formatted text? There are a couple of ways you can instruct Word to be more ...

Discover More

Entered Values are Divided by 100

Enter a value into a cell and "poof!" it is automatically divided by 100. What's going on, and how can you fix it so that ...

Discover More

Ignore Setting on Misspelled Words not Persistent

When Word flags a word as misspelled, you have some options of how to handle it. This tip explains those options and provides ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (menu)

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

Specifying a Delimiter when Saving a CSV File in a Macro

You can, within a macro, save a workbook in several different file formats that are understood by Excel. However, you may not ...

Discover More

Crashing when Searching

If you use Excel's Open dialog box to search for files and you notice that doing so ends up crashing your system, you may ...

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

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 8Mpixels. 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 6 - 3?

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.