Mastering Directory Listings in VBA: Exploring Folders and Subfolders with the Dir Function and FSO

Automating directory listings in VBA is a common requirement for Excel, Access, and other Microsoft Office solutions. Whether you need to list folders, enumerate subdirectories, or recursively scan an entire directory tree, VBA provides multiple ways to interact with the Windows file system each with distinct strengths and limitations.

In this article, you’ll learn how to generate directory listings in VBA using several proven techniques, including the built-in Dir function, the FileSystemObject (FSO) model, Shell.Application, WMI, and even PowerShell integration. We’ll explore when each approach is appropriate, how to handle recursive folder traversal safely, and why certain methods, especially Dir!, fail in nested scenarios.

If you’ve ever searched for “VBA list folders and subfolders”, “VBA recursive directory listing”, or “Dir vs FileSystemObject in VBA”, this guide walks through real-world, production-ready examples you can drop directly into your projects. By the end, you’ll know exactly which technique to use for simple folder scans, deep recursive listings, or advanced system-level automation.

Methods covered in this guide include:

• Dir function (non-recursive directory listings)
• FileSystemObject (FSO) for recursive folder traversal
• Shell.Application for Windows-based folder enumeration
• WMI for system-level and remote directory queries
• PowerShell integration from VBA

Why Automate Folder Listings in VBA?

Manually reviewing directory structures in Windows is slow, error-prone, and difficult to scale in professional environments. When working with Excel VBA, Access VBA, or other Microsoft Office automation solutions, developers often need a reliable way to list folders and subfolders, validate directory structures, or inventory file systems across multiple locations.

By automating folder listings in VBA, you can programmatically enumerate directories, traverse folder trees, and capture structured results directly inside your applications. This removes manual effort, ensures consistent results, and allows folder data to be stored, analyzed, and validated like any other dataset.

Common Use Cases for VBA Directory Automation

• Generate complete folder inventories for auditing, reporting, or documentation
• Recursively scan directories to verify required subfolders exist
• Validate production or deployment folder structures
• Populate Excel worksheets or Access tables with folder names and paths
• Drive navigation menus or linked data imports using live directory data
• Detect missing, renamed, or misconfigured folders
• Standardize file system checks across environments

Method 1: Using the Dir Function

The Dir function is VBA’s built-in method for retrieving file and folder names from a specified path. It provides a lightweight solution that does not require additional references or libraries.

This technique provides a simple, efficient listing of subdirectories which ideal for smaller directory structures or single-level folder views.

'---------------------------------------------------------------------------------------
' Procedure : ListSubDirectories
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Create a listing of directories within the specified directory and
'               append them to a table
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPath:      Full path of the root directory to extract a listing of subdirectories
'             from including trailing \
'
' Usage:
' ~~~~~~
' Call ListSubDirectories("c:\")
' Call ListSubDirectories("c:\users\")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-07-06              Initial Release - Answer website question
' 2         2024-11-04              Update input variable name to match other functions
'                                   Update Copyright
'---------------------------------------------------------------------------------------
Public Sub ListSubDirectories(ByVal sPath As String)
    On Error GoTo Error_Handler
    Dim sFolderItem           As String

    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    sFolderItem = Dir$(sPath & "*", vbDirectory)
    Do While sFolderItem <> vbNullString
        If sFolderItem <> "." And sFolderItem <> ".." Then
            If GetAttr(sPath & sFolderItem) And vbDirectory Then
                Debug.Print sFolderItem, sPath & sFolderItem
            End If
        End If
        sFolderItem = Dir$
    Loop

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ListSubDirectories" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Should you wish to say build and return an array of sub-folders, then we could modify the above into something like:

Public Function FF_ListSubDirectories(ByVal sPath As String) As Variant
    On Error GoTo Error_Handler
    Dim aFolders()            As String
    Dim sFolderItem           As String
    Dim i                     As Long

    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    sFolderItem = Dir$(sPath & "*", vbDirectory)
    Do While sFolderItem <> vbNullString
        If sFolderItem <> "." And sFolderItem <> ".." Then
            If GetAttr(sPath & sFolderItem) And vbDirectory Then
                ReDim Preserve aFolders(i)
                'aFolders(i) = sPath & sFolderItem
                aFolders(i) = sFolderItem
                i = i + 1
            End If
        End If
        sFolderItem = Dir
    Loop

    FF_ListSubDirectories = aFolders

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FF_ListSubDirectories" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

and then to use it, we would do:

Sub Test_FF_ListSubDirectories()
    Dim aFolders()              As String
    Dim sFolder                 As Variant
    Dim i                     As Long
    Const sPath = "C:\temp\test2"       'Path to extract a list of files from

    aFolders = FF_ListSubDirectories(sPath)
    If Not Not aFolders Then
        For i = LBound(aFolders) To UBound(aFolders)
            Debug.Print i + 1, aFolders(i)
        Next i
    Else
        Debug.Print "No Folders found."
    End If
End Sub

Dir’s MAJOR Limitations!

Method 2: Using File System Object (FSO)

For more advanced scenarios, especially those involving recursive folder exploration, the File System Object (FSO) offers enhanced flexibility and capability. This model, part of the Windows Scripting Runtime library, supports deeper file system interactions.

Public Function FSO_ListSubDirectories_Basic(ByVal sPath As String) As VBA.Collection
    On Error GoTo Error_Handler
    Dim oFSO                  As Object
    Dim oFldr                 As Object
    Dim oSubFldr              As Object
    Dim oSubSubFldrs          As Object
    Dim oSubSubFldr           As Object

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set FSO_ListSubDirectories_Basic = New VBA.Collection    'Total List of Files

    Set oFldr = oFSO.GetFolder(sPath)
    For Each oSubFldr In oFldr.SubFolders
        Call FSO_ListSubDirectories_Basic.Add(oSubFldr)
    Next oSubFldr

Error_Handler_Exit:
    On Error Resume Next
    Set oSubFldr = Nothing
    Set oFldr = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FSO_ListSubDirectories_Basic" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has occurred!"
    Resume Error_Handler_Exit
End Function

and would be used by doing:

Sub Test_FSO_ListSubDirectories_Basic()
    Dim oSubFolders                As VBA.Collection
    Dim oSubFolder                 As Variant
    Dim i As Long

    Set oSubFolders = FSO_ListSubDirectories_Basic("C:\Temp\Test2")     'No SubFolders
    Debug.Print oSubFolders.Count & " Folder(s) found."
    For Each oSubFolder In oSubFolders
        i = i + 1
        Debug.Print , i, oSubFolder
    Next

    Set oSubFolders = Nothing
End Sub

Recursive FSO Folder Iterations

We can take the above and modify it slightly to allow recursive folder iterations by doing:

Public Function FSO_ListSubDirectories_Recursive(ByVal sPath As String, _
                                                 Optional bListSubFldrs As Boolean = True) As VBA.Collection
    On Error GoTo Error_Handler
    Dim oFSO                  As Object
    Dim oFldr                 As Object
    Dim oSubFldr              As Object
    Dim oSubFldrFldrs         As VBA.Collection
    Dim oSubFldrFldr          As Variant

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set FSO_ListSubDirectories_Recursive = New VBA.Collection

    Set oFldr = oFSO.GetFolder(sPath)
    For Each oSubFldr In oFldr.SubFolders
        FSO_ListSubDirectories_Recursive.Add oSubFldr

        If bListSubFldrs Then
            Set oSubFldrFldrs = FSO_ListSubDirectories_Recursive(oSubFldr.Path, True)
            ' Add all subfolders from oSubFldrFldrs to the main collection
            For Each oSubFldrFldr In oSubFldrFldrs
                FSO_ListSubDirectories_Recursive.Add oSubFldrFldr
            Next oSubFldrFldr
        End If
    Next oSubFldr

Error_Handler_Exit:
    On Error Resume Next
    Set oSubFldr = Nothing
    Set oFldr = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FSO_ListSubDirectories_Recursive" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has occurred!"
    Resume Error_Handler_Exit
End Function

The function’s optional parameter bListSubFldrs allows users to control whether recursion should occur, offering flexibility for both shallow and deep scans.

An example of its usage would be:

Sub Test_FSO_ListSubDirectories_Recursive()
    Dim oSubFolders                As VBA.Collection
    Dim oSubFolder                 As Variant
    Dim i As Long

    Set oSubFolders = FSO_ListSubDirectories_Recursive("C:\Temp\Test2")  'Include files in SubFolders
    Debug.Print oSubFolders.Count & " Folder(s) found."
    For Each oSubFolder In oSubFolders
        i = i + 1
        Debug.Print , i, oSubFolder
    Next

    Set oSubFolders = Nothing
End Sub

Method 3: Shell.Application

As I have previously shown in my article VBA – Shell.Application Deep Dive we can easily use the Shell.Application object to get a listing of folders/sub-folders.

'---------------------------------------------------------------------------------------
' Procedure : Shell_ListSubFolders
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : List the subfolders of the specified folder, recursive if wanted
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Early Binding -> Microsoft Shell Controls And Automation
'             Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPath             : Folder to list subfolders of
' bProcessSubDirs   : Whether to recursively process subfolders, or not.
'                       True  => Process subfolders recursively
'                       False => Do not process subfolders
'
' Usage:
' ~~~~~~
' Shell_ListSubFolders("C:\Temps")
'   Returns => List of all subfolders, and recursively processes
'
' Shell_ListSubFolders("C:\Temps", False)
'   Returns => Top level subfolder and does not recursively process
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2020-04-22
' 2         2023-03-05              Updated for blog posting
'---------------------------------------------------------------------------------------
Function Shell_ListSubFolders(ByVal sPath As String, _
                              Optional bProcessSubFolders As Boolean = True)
    On Error GoTo Error_Handler
    #Const Shell32_EarlyBind = False   'True => Early Binding / False => Late Binding
    #If Shell32_EarlyBind = True Then
        Dim oShell            As Shell32.Shell
        Dim oFolder           As Shell32.Folder
        Dim oFolderItems      As Shell32.FolderItems
        Dim oFolderItem       As Shell32.FolderItem

        Set oShell = New Shell32.Shell
    #Else
        Dim oShell            As Object
        Dim oFolder           As Object
        Dim oFolderItems      As Object
        Dim oFolderItem       As Object

        Set oShell = CreateObject("Shell.Application")
    #End If

    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    Set oFolder = oShell.NameSpace((sPath))
    If Not oFolder Is Nothing Then
        Set oFolderItems = oFolder.Items()
        If (Not oFolderItems Is Nothing) Then
            For Each oFolderItem In oFolderItems
                'If oFolderItem.IsFolder Then '*****No good as criteria as it includes 'Compressed (zipped) Folder'?!
                If oFolderItem.Type = "File folder" Then

                    Debug.Print sPath & oFolderItem.Name
                        'oFolderItem.Path 'Same as oFolderItem.Name
                        'oFolderItem.Type
                        'oFolderItem.Size doesn't work on folders

                    If bProcessSubFolders = True Then
                        Call Shell_ListSubFolders(sPath & oFolderItem.Name, bProcessSubFolders)
                    End If
                End If
            Next oFolderItem
        End If
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set oFolderItem = Nothing
    Set oFolderItems = Nothing
    Set oFolder = Nothing
    Set oShell = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Shell_ListSubFolders" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Method 4: WMI

Once again, we can also turn towards WMI for querying the system to get such information.

Sub WMI_ListSubDirectories_Recursive(ByVal sPath As String, _
                                     Optional bListSubFldrs As Boolean = True)
    On Error GoTo Error_Handler
    #Const WMI_EarlyBind = False    'True => Early Binding / False => Late Binding
    #If WMI_EarlyBind = True Then
        Dim oWMI              As WbemScripting.SWbemServices
        Dim oCols             As WbemScripting.SWbemObjectSet
        Dim oCol              As WbemScripting.SWbemObject
    #Else
        Dim oWMI              As Object
        Dim oCols             As Object
        Dim oCol              As Object
        Const wbemFlagReturnImmediately = 16    '(&H10)
        Const wbemFlagForwardOnly = 32    '(&H20)
    #End If
    Dim sWQL                  As String

    sWQL = "ASSOCIATORS OF {Win32_Directory.Name=""" & Replace(sPath, "\", "\\") & """} WHERE AssocClass=Win32_Subdirectory Role=GroupComponent"    'Not recursive

    Set oWMI = GetObject("winmgmts:\\.\root\cimv2")
    Set oCols = oWMI.ExecQuery(sWQL, , wbemFlagReturnImmediately Or wbemFlagForwardOnly)

    For Each oCol In oCols
        Debug.Print oCol.Name
        If bListSubFldrs Then WMI_ListSubDirectories_Recursive oCol.Name
    Next oCol

Error_Handler_Exit:
    On Error Resume Next
    If Not oCol Is Nothing Then Set oCol = Nothing
    If Not oCols Is Nothing Then Set oCols = Nothing
    If Not oWMI Is Nothing Then Set oWMI = Nothing
    Exit Sub

Error_Handler:
    Debug.Print Err.Number, Err.Description
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: WMI_ListSubDirectories_Recursive" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Method 5: PowerShell

Using my PowerShell functions, it would also be possible to call a command such as:

Get-ChildItem -Path '" & sPath & "' -Recurse -Directory | Select-Object -ExpandProperty FullName

but as always, this is a slower process, so not one I recommend normally.
 
 
In the end, there’s no single “right” way to handle directory automation in VBA — it’s all about using the right tool for the job. The built‑in Dir function is perfect when you just need a quick, one‑level scan. It’s fast, clean, and easy to drop into any routine. But once recursion or structured navigation comes into play, Dir quickly runs out of steam. That’s where the FileSystemObject (FSO) steps in, giving you object‑based control and the ability to dig through complex folder trees without losing your place.

If you’re ready to level up, VBA’s got even more powerful options. WMI lets you reach deep into the system to query folder data even across remote machines. Shell.Application is yet one more good option which allows for recursive queries. And if you really want to stretch VBA’s legs, PowerShell is the ultimate sidekick, running advanced scripts and heavy lifting outside the VBA ‘sandbox’.

Bottom line: whether you’re keeping it simple with Dir, going flexible with FSO, or going pro with WMI, Shell, and PowerShell, knowing when to switch gears is the secret to building directory automation that’s smarter, faster, and way more maintainable.

Key Takeaways

• Dir is quick and simple—great for flat folder listings but not for recursion
• FileSystemObject (FSO) handles nested directories with stable, object‑based control
• WMI digs into system‑level data and supports remote access
• Shell.Application is another solid solution
• PowerShell unlocks advanced scripting and high‑performance directory operations, but much slower than other approaches

Page History