Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Importing Multiple Files to a Single Workbook

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.

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.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Cade Huff    28 Nov 2016, 23:17
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.

Sean    22 Nov 2016, 12:01
what would I change to get it to open them with sheet titles Sheet 1, sheet 2 etc and not titled as the name?
huh    30 Mar 2016, 11:40
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!
vivek pare    29 Mar 2016, 13:14
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
Andrei    21 Feb 2016, 17:54
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 !
Chux    09 Feb 2016, 16:07
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.
Ravi Kumar    13 Jan 2016, 09:22
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.
Zaki    24 Jul 2015, 11:22
God bless you. Awesome solution
Ann    15 Jul 2015, 23:28
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?
Softie    13 Apr 2015, 19:15
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
Michael (Micky) Avidan    09 Apr 2015, 06:35
@Chapers,
Try: Sheets.Add Before:=ActiveSheet
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

Chapers    08 Apr 2015, 09:36
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
Sid    20 Jun 2014, 01:01
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.
Vibhu Kunwar Chauhan    17 Jun 2014, 08:15
Very Helpful Code For Me.

Thanks Allen.
Cris    10 Jun 2014, 14:59
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!
Bernard    27 May 2014, 14:41
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!
Tim Searle    13 Oct 2013, 22:36
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?
Wojciech    13 Sep 2013, 12:34
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!
Mike    05 Sep 2013, 13: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?
Crystal    02 Aug 2013, 11:04
This was exactly what I was looking for -- what a time saver, thanks!
keiko    18 Jul 2013, 11:01
This works beautifully. Is there any way to replace name of the sheets with name of the text files?
Raghu    19 Apr 2013, 15:24
It worked. Thanks
Nathan    14 Feb 2013, 15:47
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.
carlos    31 Jan 2013, 02:40
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 ?
Tim    05 Sep 2012, 17:08
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?
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.