【VBA】Recursively List All Excel Files with VBA

VBA Code

Option Explicit

Sub main()
    
    Dim inputFolder As String
    Dim fso As Object
    
    'Set "Target Folder"
    inputFolder = "C:\Users\user\Desktop\test"
    
    Set fso = CreateObject("Scripting.FileSystemObject")

    'Call "RecursivelyAllExcelFiles Function"
    Call RecursivelyAllExcelFiles(inputFolder, fso)

    Set fso = Nothing

End Sub

Function RecursivelyAllExcelFiles(ByVal inputFolder As String, ByVal fso As Object)

    Const FILE_TYPE_XLSX As String = "xlsx"
    Const FILE_TYPE_XLSM As String = "xlsm"
    
    Dim folder As Object
    Dim file As Object
    
    'Recursion by number of subfolders
    For Each folder In fso.GetFolder(inputFolder).SubFolders
        Call RecursivelyAllExcelFiles(folder.Path, fso)
    Next

    'Repeat for number of files
    For Each file In fso.GetFolder(inputFolder).Files
        'Process Excel files only
        If LCase(fso.GetExtensionName(file.Name)) = FILE_TYPE_XLSX Or _
           LCase(fso.GetExtensionName(file.Name)) = FILE_TYPE_XLSM Then
            'Write Immediate Window
            Debug.Print file.Path
        End If
    Next

End Function

Result

You was able to do Recursively List All Excel Files with VBA.

FYI

For more information about following, see the following official online manual:

●“GetFolder method” of “FileSystemObject”

●“SubFolders property” of “Folder Object”

●“Files property” of “Folder Object”

●“GetExtensionName method” of “FileSystemObject”