[Excel VBA] How to List All Subfolders in a Folder Using FSO

Organizing or summarizing files often requires checking what subfolders exist inside a specific folder and exporting that list to an Excel sheet.

You can easily achieve this using the .SubFolders property of the FileSystemObject (FSO) Folder object.

In this article, I will explain the basic method to list subfolders directly under a specified folder, as well as an advanced technique to recursively list all nested folders.

Preparation: Reference Settings

To use FSO smoothly, I recommend enabling the reference. In the VBA Editor, go to Tools > References and check “Microsoft Scripting Runtime”.

Completed VBA Code (Immediate Subfolders Only)

The following code lists the names of all subfolders located directly under the folder where this Excel file is saved. It writes the results to column A of the active sheet.

' Reference: Microsoft Scripting Runtime
Sub ListImmediateSubfolders()

    ' Declare variables
    Dim fso As New FileSystemObject
    Dim targetFolder As Folder
    Dim subFolder As Folder
    Dim rowIndex As Long
    
    '--- 1. Get the parent folder object ---
    Set targetFolder = fso.GetFolder(ThisWorkbook.Path)
    
    ' Clear the sheet and set headers
    ActiveSheet.Cells.ClearContents
    ActiveSheet.Range("A1").Value = "Subfolder Name"
    ActiveSheet.Range("B1").Value = "Full Path"
    rowIndex = 2

    '--- 2. Loop through the .SubFolders collection ---
    For Each subFolder In targetFolder.SubFolders
        '--- 3. Write subfolder info to the sheet ---
        ActiveSheet.Cells(rowIndex, "A").Value = subFolder.Name
        ActiveSheet.Cells(rowIndex, "B").Value = subFolder.Path
        rowIndex = rowIndex + 1
    Next subFolder
    
    ActiveSheet.Columns("A:B").AutoFit ' Auto-fit column width
    
    MsgBox "Subfolder listing complete."
    
    ' Release objects
    Set subFolder = Nothing
    Set targetFolder = Nothing
    Set fso = Nothing

End Sub

Explanation of Key Points

1. fso.GetFolder(Path)

First, use this method to retrieve information about the parent folder as a Folder object.

2. .SubFolders Property

For Each subFolder In targetFolder.SubFolders

This is the core of the process. The .SubFolders property returns a collection of all subfolders directly inside that folder. By using a For Each loop, you can process each subfolder one by one as a Folder object (stored in the variable subFolder).

3. Writing Information

Inside the loop, since the subFolder variable is a Folder object, you can access various properties like .Name (folder name), .Path (full path), and .DateCreated to write them to the sheet.

[Advanced] Recursively Get Subfolders of All Levels

The basic .SubFolders property only retrieves the immediate children. If you want to list “grandchildren” folders and deeper levels, you need to use a technique called “Recursion”.

'--- Main Sub to start execution ---
Sub ListAllSubfoldersRecursively()
    Dim fso As New FileSystemObject
    Dim startFolder As Folder
    
    Set startFolder = fso.GetFolder(ThisWorkbook.Path) ' Set start folder
    
    ActiveSheet.Cells.ClearContents
    
    ' Call the recursive process
    GetSubfolders startFolder, 0
    
    MsgBox "All levels of subfolders have been listed."
End Sub


'--- Recursive Sub that calls itself ---
Private Sub GetSubfolders(ByVal parentFolder As Folder, ByVal indentLevel As Integer)
    Dim subFld As Folder
    
    ' Loop through immediate subfolders of the parent
    For Each subFld In parentFolder.SubFolders
        ' Output folder name with indentation based on depth
        ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = _
            String(indentLevel * 4, " ") & subFld.Name
            
        ' ★ Call itself to search deeper levels
        GetSubfolders subFld, indentLevel + 1
    Next subFld
End Sub

In the GetSubfolders procedure, the code calls itself. This repeats the process: “Find a subfolder -> Search inside that subfolder,” until no more folders are found, effectively listing the entire folder structure.

Summary

• To get a list of immediate subfolders, loop through the .SubFolders collection of the Folder object.
• To target all levels, use “Recursive Processing” to repeat the search for each found folder.

By mastering the FSO .SubFolders property, you can automate advanced tasks such as analyzing complex folder structures or searching for files across multiple directory levels.