Recursive Folder Search To Combine CSV TO Master

Question

I have been using this - but it is not recursive. How can this be adapted to be a recursive file search to merge about 100 .csv files into one workbook?

    Sub test() 
    Dim myDir As String, fn As String, wb As Workbook 
    Set wb = ActiveWorkbook 
    With Application.FileDialog(msoFileDialogFolderPicker) 
        If .Show Then myDir = .SelectedItems(1) & "\" 
    End With 
    If myDir = "" Then Exit Sub 
    fn = Dir(myDir & "*.csv") 
    Do While fn <> "" 
        With Workbooks.Open(myDir & fn) 
            .Sheets(1).Copy after:=wb.Sheets(wb.Sheets.Count) 
            .Close False 
        End With 
        fn = Dir 
    Loop 
End Sub

[This older answer on cycling through sub folders might help you](https://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory) — Pslice, Sep 11 '17 at 17:50

score 1 · Accepted Answer · answered Sep 11 '17 at 18:00

Here is the main structure that you probably want. Depending on if you want to process the first folder (Option 1) or just the Sub-Folders (Option 2); choose the respective option for placing your code (replacing Debug.Print Path & Folder)

Main Function:

Sub MainListFolders()
    ListFolders ("C:\Temp\")
End Sub

Recursive Function:

Sub ListFolders(Path As String)
Dim Folder As String
Dim FolderList() As String
Dim i As Long, Count As Long
    Folder = Dir(Path, vbDirectory)
    ' Option 1: Can process folder here
    'Debug.Print Path & sFolder
    Do While Folder <> vbNullString
        ' Check that it is a Folder
        If CBool(GetAttr(Path & Folder) And vbDirectory) Then
            ' We don't want to include the Current (".") or Previous ("..") folders, so..
            If Replace(Folder, ".", vbNullString) <> vbNullString Then
                ' Option 2: Can process folder here
                Debug.Print Path & Folder
                ' Store the list of Sub-Folders to recursively check at the end
                ' If you try to do a recursive call here, when it jumps back, it wont be able to process the next Dir()
                ' because the Dir() folder would have changed in the recurive call.
                ReDim Preserve FolderList(Count)
                FolderList(Count) = Folder
                Count = Count + 1
            End If
        End If
        Folder = Dir()
    Loop
    ' Do the recursive calls here
    For i = 0 To Count - 1
        ' Make sure to add the "\" to the end
        ListFolders Path & FolderList(i) & "\"
    Next
End Sub

Recursive Folder Search To Combine CSV TO Master

1 Answers1