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
.SubFolderscollection 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.
