Loading
ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

Importing Multiple Files to a Single Workbook

Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier 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 (10400) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Importing Multiple Files to a Single Workbook.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

 

Comments for this tip:

Percy    19 Aug 2014, 09:30
How would I manipulate this code to make the files open in the same workbook?

Thanks much
Vinayak L    18 Aug 2014, 11:16
Hey its really a brilliant macro ive ever seen but, when i went through ur code i found some errors while adding a extra text or special characters so i made a bit changes and check out this below code(this code was modified as from ref: from above file)
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:=True, Semicolon:=False, _
      Comma:=False, Space:=False, _
      Other:=False, 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:=True, Semicolon:=False, _
              Comma:=False, Space:=True, _
              Other:=False, 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
    ChDir "C:\Documents and Settings\Admin\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Admin\Desktop\Book1.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Range("A1").Select
    Application.Run "Book1.xlsm!testcode"
    Columns("A:A").ColumnWidth = 22.29
    Columns("B:B").ColumnWidth = 75.29
    Columns("C:C").ColumnWidth = 42.14
    ActiveWindow.Zoom = 115
    ActiveWindow.Zoom = 130
    ActiveWindow.Zoom = 145
    ActiveWindow.Zoom = 160
    ActiveWindow.Zoom = 175
    ActiveWindow.Zoom = 190
    ActiveWindow.Zoom = 205
    Range("B1").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B2").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A4").Select
    Columns("A:A").ColumnWidth = 48.14
    ActiveWindow.Zoom = 190
    ActiveWindow.Zoom = 160
    ActiveWindow.Zoom = 145
    ActiveWindow.Zoom = 130
    ActiveWindow.Zoom = 115
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.Zoom = 100
    ActiveWindow.Zoom = 85
    ActiveWindow.SmallScroll Down:=-12
    Range("A1").Select
    ActiveWindow.SmallScroll Down:=-18
    ActiveWindow.Zoom = 100
    ActiveWindow.Zoom = 115
    Windows("Book1.xlsm").Activate
    Windows("Book2").Activate
    ActiveWindow.Close
    Application.Run "Book1.xlsm!testcode"
    Windows("Book1.xlsm").Activate
    Windows("Book3").Activate
    ActiveWindow.Close
    Application.Goto Reference:="testcode"
    ActiveWorkbook.Save
    Application.Run "Book1.xlsm!testcode"
    Columns("B:B").ColumnWidth = 41.86
    ActiveWindow.SmallScroll Down:=12
    ActiveWorkbook.Save
    Windows("Book1.xlsm").Activate
    Application.Run "Book1.xlsm!testcode"
    Range("J12").Select
    ActiveCell.FormulaR1C1 = ""
    Columns("C:C").Select
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Range("B17").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("A:A").ColumnWidth = 16.71
    Columns("A:A").ColumnWidth = 31.29
    Range("A1").Select
    Windows("Book4").Activate
    ActiveWindow.Close
    Application.WindowState = xlMinimized
    ActiveWindow.Close
    Application.Run "Book1.xlsm!testcode"
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 20.14
    Columns("B:B").ColumnWidth = 34.86
    Columns("C:C").ColumnWidth = 16.86
    Application.WindowState = xlMinimized
    Windows("Book1.xlsm").Activate
    ActiveWorkbook.Save
    ActiveWorkbook.Save
    Application.Run "Book1.xlsm!testcode"
    Columns("A:A").ColumnWidth = 15.29
    Columns("B:B").ColumnWidth = 16
    Columns("C:C").ColumnWidth = 14.71
    Columns("D:D").ColumnWidth = 26.43
Logeshwaran    25 Jun 2014, 04:04
Hi

I have used macros in seperate tabs in the same excel sheet.

For eg:
I have used macros seperately in 3 tabs in a sheet,but my requirement is that i want to use in a single tab commonly.

Can you pls provide solution ASAP.



Ashish kumar     17 Jun 2014, 00:56
Thank you very much for your this post it helps me a lot to get what i want.
Ankur Khurana    16 Dec 2013, 04:20
Hi Aviva,
I believe what you said it Correct, I am getting runtime error when the macro is trying to read the second text file in the folder.

Currently it is writing only first file, can you please help me with the fix for the same.
Sun    16 Jul 2013, 07:49
Hi Allen,

Your macro works perfectly except for one thing. When all the text files have been transported into a workbook on different sheets, instead of the cells formatted to contain numbers, I seem to get them formatted to contain texts. Could you please help me with this?

Also, I would like to have all the text files in the excel to be presented in graphs. One thing to note is that the text files have different number of columns.

So far, I used the first macro.

Thank you in advance
Aviva Knoll    04 Jun 2013, 05:59
I found out why I kept getting the run-time error. Your macro is wonderful but has the following limitations:
1. Your Excel file needs to have at least as many tabs as there are text files to be imported.Meaning tabs are not added automatically.
2. The macro cannot handle empty text files.

Thanks for your macro
Aviva
Aviva Knoll    03 Jun 2013, 03:01
Hi Allen,
When I try to run your macro, I get a run-time error 7 (out of memory). When debugging, the following line in the code is highlighted:
.Refresh BackgroundQuery:=False
Do you have any idea why this happens?

The Excel file is stored in the same folder as the text files to be imported. I have changed fpath to point to this folder.

Thanks
Aviva
Yaseer    20 Feb 2013, 04:32
Hi Allen

I've used your code and it works. I want to import all the text files on to one worksheet so I made a few changes like selecting only sheet1 and making the destination cell a variant.
When I run it, it clears the data from the previous loop before pasting the new data. So once it has imported file 1 on sheet 1 it then clears it before importing file 2 on sheet 1.

Can you please explain why?

Regards
Jim    07 Dec 2012, 10:29
I've been using this to combine Excel files into a Master workbook. But I have two other large macros to run on the data in the Master. So I set up a template with the three macros. But when this macro runs, it creates the Master as a new workbook (ex: Book2). I want the files pulled into the template file so the other macros can run.
Jim    07 Dec 2012, 10:20
FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Excel Files (*.xls), *.xls", _
      MultiSelect:=True, Title:="Excel Files to Open")
Greg Mouning    30 Nov 2012, 12:27
Hi Allen,

I would like to do this except the files I want to use are Excel not Text. Is there an easy solution to do this with your code?

Thanks,
Greg

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 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

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

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.