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.

Importing Multiple Files to a Single Workbook

by Allen Wyatt
(last updated December 29, 2014)

43

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.

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

Saving Personalized Copies of a Document

Need a series of documents that include an individual's name or a company name? Here's a handy little macro that will make ...

Discover More

Different Layouts for Footnotes

If you want to have footnotes appear in a different number of columns than what your text appears in, you may be out of luck. ...

Discover More

Counting Fields in a Document

Need to count the number of times a particular field appears in a document? It's easy to do when you apply the techniques ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Opening a Workbook with Two Windows

If you open a workbook and notice that Excel displays two windows for it, this has to do with how the workbook was saved. ...

Discover More

Comma-Delimited and MS-DOS CSV Variations

Excel provides different file formats you can use to export your worksheet information. One such file format is CSV, or comma ...

Discover More

Avoiding Scientific Notation on File Imports

When importing information from a CSV file, you may get unintended results from time to time. Here's how to force Excel to ...

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

2017-05-09 07:50:47

Joseph

The code is giving no data was selected to parse


2017-02-01 11:22:40

Mike

Worked great! Changed delimeter to what I needed and ran code, worked perfectly.


2017-01-25 08:50:29

Marie Lamb

Hi i am really desperate and need to do something in Excel and i can't find any information anywhere on how it is done... I have created a VBA macro in excel that imports data into a word document. That works fine but when i create the same macro with different text i need it to import the information into the same word document - i either get an error or it opens a new word document. I don't mind paying for this information if you can help me sort it out or if you can point me in the right direction of someone that can help that would be fantastic. here is the VBA code that works; Many thanks for your help...
Sub copytoworddDoc()
Dim objWord
Dim objDoc
Dim objSelection
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open("C:usersmlambDocumentsregister.docx")
objWord.Visible = True
Set objSelection = objWord.Selection
objSelection.TypeText ("This is my text in Word Document using Excel hit the button and all this text will be imported into word")
End Sub


2016-11-04 04:23:40

Arno

I use the code of
Vinayak L 18 Aug 2014, 11:16
and it works...but it will only take the data from the first file correct.
In the second file it sees space for a colomn and he doesn't import all columns.

I need a script...that imports textfiles based on tabs..get every file in a separate sheet with the name of the file as the sheet name


2016-09-15 14:04:58

Dave D

Awesome, works great! Not sure why, but it is deleting all the capital "F"s and inserting a blank cell and moving the rest of the text over a cell. The only change I made was to "False" the pipe delimiter and .TextFileTabDelimiter = True


2016-08-23 01:58:50

Thierry

Thanks a lot. It's very great. Just copy paste and everything run exactly


2016-05-31 15:53:23

Alejandro

Thank you Allen, I used the second cscript, this is 99% of the code I need.
However, I am also looking for something similar as to what other users have been asking for.

First, I changed the .txt to .csv, so far the script hasn't worked with that change.
Second, similar to what user 'Ashni' asked, I need for all .csv files in the same folder to populate an already existing .xlsm workbook. Thus, these .csv files would fill every consecutive worksheet in the workbook without deleting anything on Sheet1.
Is this possible.
Thank you very much in advance.


2016-05-25 05:16:02

Ashni

Hi,
This code is great, thank you! How can I modify it to import all the .txt files on to 1 specified worksheet in an existing workbook?


2016-05-08 10:10:20

Pradeep

Original text file contain this data format-
GOLD16JUNFUT 05/02/2016 10:00 30300 30300 30268 30279 25
-And after import it looks like-
GOLD16JUNFUT|05/02/2016 10:00|30300 30300|30268|30279|25
*Seprater'|' as Column Line
-But i want this look like-
GOLD16JUNFUT|05/02/2016|10:00|30300 30300|30268|30279|25
-05/02/2016 10:00
Date separated from time by space and they should come in different column.
Plz help!


2016-05-08 10:00:28

Pradeeep

Sub CombineTextFiles() work well except delimiter for space.
I turned Space:=True but it didn't work.
Plz give solution!


2016-04-12 10:09:58

chris

ref:

I am needing to pull a specific range of data from 250 or so .txt files into one workbook such that the range from each has a separate column but not a separate worksheet.

How would I do this?

Thanks!

I need to do the same. I need just one piece of info from multiple text files (864) to be displayed in the same sheet of a workbook.

Can you help please????

Thank you so much. I am using 365 version.


2016-03-29 18:36:51

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-08 14:14:45

Kristi F

Hi,

I am needing to pull a specific range of data from 250 or so .txt files into one workbook such that the range from each has a separate column but not a separate worksheet.

How would I do this?

Thanks!


2016-02-15 16:50:37

BBB

How can I modify this code to open each file in a new column, for example File 1 in column A, File 2 in column B, etc.

Thanks


2016-02-09 04:34:56

BMO

I've been using the second set of example code to simultaneously import a series of CSV files into an excel document. The issue is I need to import them all into one workbook. I have tinkered with the code to allow me to do so, however, when importing the files, they are imported alongside eachother, as opposed to underneath eachother as if the data were imported from one continuous text file. Would someone be able to help me define a range of columns to limit the imports to?


2016-01-28 08:14:21

kalidass.s

how to control a comment button with another one command button.
expl.

there is two command button in a sheet called
command button-1 and command button-2

command button-1 contains the following macro
Range(“A1”).Select

so I want to activate the command button-1 by pressing the command button-2

please give me a solution


2016-01-12 00:53:07

Corey D

Couldn't get the macro published by Allen to work, but I used "Vinayak L"'s macro and it worked just fine for me. I did some minor edits as I found the first file imported had different delimiters specified than the subsequent files.

Also discovered that if your file names are identical for the first 25 characters or so, excel wont be able to handle them. They need to have varied names within those first 25 or so characters.

Thanks a bunch!


2015-12-07 13:58:11

F_Burr

This code saved me so much time. I am curious if you can help me with one thing though. I need each of the sheets to have the same name as the txt file they come from do you know of a way to do this or can you point me in a direction for help?


2015-12-06 17:32:30

Ivan Spector

Thank you so much for the above code. You have no idea. I do physical chemistry and work with a variety of data types and formats as well as a variety of programs to treat and analyze it. The above code saved me a lot of time. My sincere thanks.


2015-11-13 09:07:55

Dilman

Thanks a bunch! Perfectly what I needed.
I just applied the formatting that my recorded macro showed (commaseparated).

Now I just need to find another macro to stack the sheets into one sheet.

(but most often I want them on separate sheets).


2015-09-15 22:11:45

joseph

Hi,

When the above macro runs, it creates a new workbook. I want the files open in the same workbook within this macro so the other macros in this workbook can run as well.


Thanks much


2015-07-16 01:46:43

A_ver

How do I modify this code to import excel files instead of text files?


2015-04-24 08:44:03

Lewis

This is a very nice piece of code.

How would I modify it to import the files into new sheets within the currently active workbook rather than all new sheets in a new workbook?

Thanks


2015-03-03 14:34:18

Austin Abair

Hello,
I have been using some of the code throughout this thread. I working with .txt files and may need to import 1:M files in 1 worksheet. With these files, I need to append them one under the other. Any ideas on how to do that? I have the code to import a .txt file which works fine and was wondering how to integrate that with the initial code above.

Thanks so much,

Da Bears


2015-03-02 10:50:53

imsaying

Hi Aviva
I found the same problem. I changed one line and now it works fine.
Please replace
Sheets("Sheet" & idx).Select
by
Sheets.Add.Name = fname

It will create a sheet named each time and furthermore name the sheet as the name of the txt file.
maybe we can delete ibx totally in this case? i am not sure. It's my first day to know VBA...


2015-02-25 10:30:52

AK

Hi Allen,

Thanks for such wonderful code.
I have a question though.

How do I run this code from a Button in Excel?

Because when I run the code, it opens and imports files in a new wbk.

Thanks again!
Asar


2015-01-05 23:27:24

dan

hi guys,

im trying to insert data from text multiple files. but when the first file isn't there, the macro stops to a halt and doesn't continue inserting the others.

Q: how can i make the macro jump to the next line if the first file is not found?

thanks in advance.
Done


2014-11-27 08:09:17

Rakesh

Hi Alen,

Your first macro of importing all the excel files is running perfectly, my query is I want to import it a specific excel file saved on specific folder of my had drive. Also when all the tabs are imported I want tab name to reflect the file name so that I am able to verify from which file the sheet is imported.

Rgd,s
Rakesh


2014-10-20 12:41:10

Bigger Don

@Percy
The way I read the code in the first macro it puts all of the files into a single workbook.


2014-10-20 12:27:06

Bigger Don

@Jamer
Working with Word files (doc or docx) is a different animal, they are inserted not opened. Here's the key to your answer. When I recorded inserting a docx file into a worksheet I got the following:
ActiveSheet.OLEObjects.Add(Filename:= _
"C:Users<username>DocumentsTest File 2014-09-30.docx" _
, Link:=False, DisplayAsIcon:=False).Select ActiveSheet.OLEObjects.Add(Filename:= _
"C:Users<username>DocumentsTest File 2014-09-30.docx"" _
, Link:=False, DisplayAsIcon:=False).Select

So, the answer to your question is to * use the Alan's first macro as a starting point
* inside the "While x <= UBound(FilesToOpen)" loop
-- Remove the existing code
-- Add a worksheet (ActiveWorkBook.Worksheets.Add) It is now the active worksheet
-- Add a version of a version of the above, replacing "C:Users<username>DocumentsTest File 2014-09-30.docx" with "FilesToOpen(x)" (no quotes)


2014-10-17 12:01:51

Jamer

What if I have 35 .docx files i would like to import into Excel?


2014-08-19 09:30:09

Percy

How would I manipulate this code to make the files open in the same workbook?

Thanks much


2014-08-18 11:16:14

Vinayak L

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 SettingsAdminDesktop"
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and SettingsAdminDesktopBook1.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


2014-06-25 04:04:09

Logeshwaran

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.




2014-06-17 00:56:36

Ashish kumar

Thank you very much for your this post it helps me a lot to get what i want.


2013-12-16 04:20:01

Ankur Khurana

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.


2013-07-16 07:49:26

Sun

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


2013-06-04 05:59:54

Aviva Knoll

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


2013-06-03 03:01:14

Aviva Knoll

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


2013-02-20 04:32:08

Yaseer

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


2012-12-07 10:29:13

Jim

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.


2012-12-07 10:20:11

Jim

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Excel Files to Open")


2012-11-30 12:27:27

Greg Mouning

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


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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.