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:

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:

read-and-combine-all-files-in-a-folder


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Comments and Trackbacks

  1. 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

  2. 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,

  3. Siusiu wrote:

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

  4. 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.

  5. Deepesh wrote:

    Awesome. thanks

  6. 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.

  7. 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

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES