Categorized | excel download, excel macro and vba

VBA Read List and Combine All Excel CSV Text files in a Folder or Path

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:

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

What Do You Think ?

Comments and Trackbacks

Amith wrote:

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 am

Ajay wrote:

Hi,

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.

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

Sub List_All_The_Files_Within_Path()

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 pm

Siusiu wrote:

I the files have multiple worksheet, how could I combine a specific worksheet called “ABC”? Would you please help?

January 23rd, 2010 at 12:18 pm
Paul Vaglio wrote:

This 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 pm
Deepesh wrote:

Awesome. thanks

May 8th, 2012 at 5:35 am
Venkatesh VR wrote:

While 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 am
Guiso wrote:

Hi 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,
Guiso

June 29th, 2013 at 1:04 am