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.
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: 
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
Note:
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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
If you import data into Excel that is created by other programs, you know that it can be bothersome to get your data ...
Discover MoreIf you get errors in Excel that your filenames are too long, it can be confusing and frustrating. Applying the ideas ...
Discover MoreNeed to know what the full path name is for the current workbook? With a simple macro you can display the full path name ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-03-17 05:05:27
@Denise
Allen Wyatt has a Tip for copying Excel Worksheets from many workbooks to a single Workbook
https://excel.tips.net/T007425_Combining_Worksheets_from_Many_Workbooks.html
Alan Elston
2018-03-13 18:08:02
Denise
Would the tip:
Importing Multiple Files to a Single Workbook
Work for individual excel files?
2018-01-10 19:12:18
Cameron Stewart
For csv.
Using CombineTextFiles() macro
Changed
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.csv), *.csv", _
MultiSelect:=True, Title:="Text Files to Open")
2017-11-28 09:31:29
Dex Luther
The first version of the code is pretty awesome! Works really well.
The only thing I haven't been able to figure out is how to get it to move all the tabs into the current workbook instead creating a new one. I keep getting errors when I try.
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
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
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
@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
@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
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments