Here’s the VBA code to read, list and combine all excel, csv, text any other format file in to a single Excel sheet. By editing the code, you can make the program read and then combine files of only a specified extension say, Excel (.xls) or comma separated (.csv) or text (.txt). If you have a lot of small files getting FTP’d over or getting accumulated in a location, this can save a lot of time.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | Sub List_All_The_Files_Within_Path() Dim File_Path As String Dim cell_to_paste_next_dataset As Range Dim active_workbook, dataset_workbook As Workbook Dim active_sheet As Worksheet Application.DisplayAlerts = False 'USE THIS IF YOU WANT TO PLACE THE FILE IN THE SAME DIRECTORY WHERE ALL THE OTHER FILES ARE PRESENT File_Path = ActiveWorkbook.Path 'OR YOU CAN SPECIFY A CUSTOM PATH 'File_Path = "C:\My_Folder" Set cell_to_paste_next_dataset = Cells(1, 1) Set active_workbook = ActiveWorkbook Set active_sheet = ActiveSheet With Application.FileSearch .NewSearch .LookIn = File_Path .Filename = "*.*" 'CHANGE FILE EXTENSION TO OTHER EXCEL BASED EXTENSION IF YOU WANT .SearchSubFolders = False .Execute For file_count = 1 To .FoundFiles.Count If File_Path & "\" & active_workbook.Name <> .FoundFiles(file_count) Then Workbooks.Open Filename:=.FoundFiles(file_count) Set dataset_workbook = ActiveWorkbook Range(ActiveCell.SpecialCells(xlLastCell), Cells(1)).Copy active_sheet.Activate Cells(ActiveCell.SpecialCells(xlLastCell).Row, 1).Select ActiveSheet.Paste dataset_workbook.Close End If Next file_count End With End Sub |
If you need to customize the code, there are two things that you may want to change:
File_Path : Specifies the path in which the files are located. By default, the code is configured to combine files from same folder in which it is located. However, you want, you can specify a particular location from which the files need to be picked up and combined.
.Filename : Specifies the file extension that you would like to combine. You can either specify all (*.*) or any particular extension (*.xls, *.csv etc) and files of only those extensions will be picked up for collation.
VBA Code to Read, List and Combine All Excel CSV Text files in a folder or path in Excel 2007
Reader Amith pointed out that the above code does not work for Excel 2007. A little research brought to light the fact that the Application.FileSearch method had been made hidden in Excel 2007 that would cause the above code not to produce the desired result. Here’s the code that will work for Excel 2007.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 'THIS FUNCTION WORKS SHOULD WORK FOR ALL VERSIONS OF EXCEL 'ORIGINAL CODE http://www.mrexcel.com/forum/showthread.php?p=1228168 BY NATE OLIVER 'MODIFIED BY AJAY (www.databison.com) Sub List_All_The_Files_Within_EXCEL_2007() Set cell_to_paste_next_dataset = Cells(1, 1) Set active_workbook = ActiveWorkbook Set active_sheet = ActiveSheet Application.DisplayAlerts = False 'USE THIS IF YOU WANT TO PLACE THE FILE IN THE SAME DIRECTORY WHERE ALL THE OTHER FILES ARE PRESENT File_Path = ActiveWorkbook.path 'OR YOU CAN SPECIFY A CUSTOM PATH 'File_Path = "C:\My_Folder" strName = Dir(File_Path & "\" & "*.*") 'CHANGE FILE EXTENSION TO OTHER EXCEL BASED EXTENSION IF YOU WANT Do While strName <> vbNullString If active_workbook.Name <> strName And strName <> "" Then Workbooks.Open FileName:=File_Path & "\" & strName Set dataset_workbook = ActiveWorkbook Range(ActiveCell.SpecialCells(xlLastCell), Cells(1)).Copy active_sheet.Activate Cells(ActiveCell.SpecialCells(xlLastCell).Row, 1).Select ActiveSheet.Paste dataset_workbook.Close End If strName = Dir Loop Application.DisplayAlerts = True End Sub |
If you need to customize the code, there are two things that you may want to change:
File_Path : Specifies the path in which the files are located. By default, the code is configured to combine files from same folder in which it is located. However, you want, you can specify a particular location from which the files need to be picked up and combined.
strName :
You can change the file extension in the line to specify a custom file format.
Ex. change strName = Dir(File_Path & “\” & “*.*”) to strName = Dir(File_Path & “\” & “*.xls”) to read all Excel files and change to strName = Dir(File_Path & “\” & “*.txt”) to read all text files.
You can download a sample worksheet with the VBA code to combine all files in a folder here or click on the button below:
Excel Chart Maximum Y Axis Value and Axis Interval (Major Unit) 
Prevent Users Ctrl Break (ing) Your VBA Code During Execution 
Multithreaded VBA – An Approach To Processing Using VBScript 
VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code 
Treemap Add-In For Excel – Treemap Software Demo 
Treemap in Excel – Coming Soon 
This macro does not seem to work on Excel 2007, with application.search is the point where the program is hanging.
Can you tell me what seems to be the problem.
thanks & regards,
Amith
August 31st, 2009 at 5:52 amHi,
Application.FileSearch was made a hidden function in Excel 2007. This thread by Nate Oliver should help you list all files in a directory. Here is the modified code that can help you combine all files in directory. As I said, this will combine all files in a directory and does not work on specific file extensions.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Dim strName As String
Dim strArr(1 To 65536, 1 To 1) As String, i As Long
strDir = ActiveWorkbook.Path
Const searchTerm As String = ""
Set cell_to_paste_next_dataset = Cells(1, 1)
Set active_workbook = ActiveWorkbook
Set active_sheet = ActiveSheet
Let strName = Dir$(strDir & "\" & "*.*")
Do While strName <> vbNullString
Let i = i + 1
Let strArr(i, 1) = strDir & "\" & strName
Let strName = Dir$()
If active_workbook.Name <> strName And strName <> "" Then
Workbooks.Open Filename:=strDir & "\" & strName
Set dataset_workbook = ActiveWorkbook
Range(ActiveCell.SpecialCells(xlLastCell), Cells(1)).Copy
active_sheet.Activate
Cells(ActiveCell.SpecialCells(xlLastCell).Row, 1).Select
ActiveSheet.Paste
dataset_workbook.Close
End If
Loop
End Sub
regards,
August 31st, 2009 at 2:06 pmI the files have multiple worksheet, how could I combine a specific worksheet called “ABC”? Would you please help?
January 23rd, 2010 at 12:18 pmThis was a real time saver for me!
However, I did have one problem. The last record/row of each file was overlaid by the next paste. I found a workaround, but it wasn’t pretty.
FYI I was dealing with CSV files in Excel 2003 with XP.
March 14th, 2012 at 2:45 pmAwesome. thanks
May 8th, 2012 at 5:35 amWhile excuting the vba code in the excel read-and-combine-all-excel-csv-and-txt-files-in-a-folder.xls We are getting the error message
Run-time error ‘445″ Object doen’t support this action
Please let us know how to correct this issue.
January 29th, 2013 at 4:47 amHi Paul Vaglio,
to prevent overlaping new paste on last row, replace the similar sentence in the IF function with this one:
Cells(ActiveCell.SpecialCells(xlLastCell).Row + 1, 1).Select
Cheers,
June 29th, 2013 at 1:04 amGuiso