Excel VBA : Loop through all the files and subfolders in a Folder

Hi All,

I write this function many many times in my projects. When we have to create a temporary folder which are specific to our application, then I normally have to loop through all the files in a folder and put some validations so that my code should not replace any previous temp or log files. So I thought to share a small snippet of Excel VBA code where I am trying to search all the files in a Folder ( and its subfolders too) and printing in an Excel Worksheet.

Private fileCounter As Integer
Private activeSht As Worksheet
'Display all the files in a folder. Searches all the sub folders.

'Prints Folder Names in Column A and and the file Names in Column B

Sub SearchFiles()
    Dim pth As String
    Dim fso As FileSystemObject
    Dim baseFolder As Folder
                    
    pth = "C:\Projects\" 'the base path which has to be searched for Files
    Set fso = New FileSystemObject
                    
    ''check if the folder actually exists or not
                    
    If (Not (fso.FolderExists(pth))) Then
        'the folder path is invalid. Exiting.
        MsgBox "Invalid Path"
        Exit Sub
    End If
    
    Set baseFolder = fso.GetFolder(pth)
    
    fileCounter = 1
    Set activeSht = ActiveSheet
    
    activeSht.Range("A1").Value = "Folder Name"
    activeSht.Range("B1").Value = "File Name"
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    PrintFileNames baseFolder
    
ErrHandler:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub
    
    
Sub PrintFileNames(baseFolder As Folder)
    Dim folder_ As Folder
    Dim file_ As File
    
    For Each folder_ In baseFolder.SubFolders
        'call recursive function.
        PrintFileNames folder_
    Next folder_
    
    For Each file_ In baseFolder.Files
        'print files here
        activeSht.Range("A1").Offset(fileCounter, 0).Value = baseFolder.Path
        activeSht.Range("B1").Offset(fileCounter, 0).Value = file_.Name
        fileCounter = fileCounter + 1
    Next file_
End Sub
    

The above code loops through all the subfolders and the files in a given folder and prints the folder names in Column A and the file Names in Column B.

HTH,
Vikas