Get List of All Subfolders in a Folder – Excel VBA

Excel VBA Dir – Directory Listing – All Subfolders

The macro will fetch all subfolders & folders under subfolders till its last child folder.

You just have to input the root folder path for this macro.

It is a complete code that will search or process all the files in a root folder.

Excel VBA – All Subfolders – List in Folder

Just copy paste this code into your VBA project.

It will loop through all the subfolders in the root directory & present the directory listing in worksheet2.

Public oSh As Worksheet

'Process Main folder
Sub Get_All_Sub_Folders_List()
    Dim Root_Folder_Path As String, fso As Object, xFolder As Object
    Dim iSh As Worksheet
    Set iSh = ThisWorkbook.Sheets(1)
    Set oSh = ThisWorkbook.Sheets(2)
    oSh.Cells.ClearContents
    oSh.Activate
    
    'Get Root Folder Name
    Root_Folder_Path = iSh.Range("B1")
    
    'Get Sub Folder List to Temp Calc Sheet
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set xFolder = fso.getFolder(Root_Folder_Path)
    
    'Get Subfolders From Roor Folder
    List_Subfolders xFolder
    
    'Process Completed
    oSh.Columns.AutoFit
    MsgBox "Process Completed"
End Sub

'Process Subfolders - Loop Reccursize till last sub folder
Sub List_Subfolders(ByRef objFolder As Object)
    'Declare Variables
    Dim SubFolder As Object, xSubFolder As Object, xRow As Double
    
    'Get Empty Row
    xRow = 1
    While oSh.Cells(xRow, 1) <> ""
        xRow = xRow + 1
    Wend
    
    'Write Each Subfolder
    For Each SubFolder In objFolder.SubFolders
        oSh.Cells(xRow, 1).Value = SubFolder.Path & "\"
        oSh.Cells(xRow, 2).Value = "'" & SubFolder.Name
        oSh.Cells(xRow, 3).Value = "'" & SubFolder.Size & " bytes"
        Application.StatusBar = "Folders Processed: " & xRow
        oSh.Cells(xRow, 1).Select
        xRow = xRow + 1
        DoEvents
    Next SubFolder
    
    'Recurrsive Loop To find Subfolders inside Each subfolder
    For Each xSubFolder In objFolder.SubFolders
        List_Subfolders xSubFolder
    Next xSubFolder
    
    'End of Sub
End Sub

Few instructions about the code:

1. iSh is the input worksheet name
2. Input root folder path in cell B1 of iSh
3. oSh refers to the Output sheet
4. At every run, the Output sheet content will be erased.