Excel VBA: Loop Through Folders and Subfolders

VBA & macros · Updated November 22, 2026

To loop through folders and subfolders in Excel VBA, use the FileSystemObject with a recursive procedure, or the Dir function with a stack-based approach. The most reliable method is a recursive subroutine that calls itself for each subfolder, combined with FileSystemObject for cleaner path handling.

Why You Need Folder Looping in VBA

Excel VBA is often used to automate file management tasks: importing data from multiple workbooks, renaming files, moving reports, or cleaning up old files. Without a loop, you would have to hard-code every file path, which breaks as soon as the folder structure changes. A folder loop lets your macro adapt to any directory tree, processing every file that matches your criteria, no matter how deep it sits.

Two Main Approaches: Dir vs. FileSystemObject

VBA offers two primary ways to enumerate files and folders:

  • Dir function – built-in, no references needed, but it has quirks (you must call it with an empty string to reset, and it doesn’t easily handle recursion without manual stack management).
  • FileSystemObject (FSO) – requires a reference to “Microsoft Scripting Runtime” (or late binding), but provides a cleaner object model with Folder and File objects, making recursion straightforward.

For most recursive tasks, FSO is the better choice because it returns actual objects with properties like Name, Path, and DateLastModified, and it handles subfolder traversal without string parsing.

How to Set Up FileSystemObject

Before using FSO, you need to create an instance. You can either add a reference (Tools > References > Microsoft Scripting Runtime) or use late binding with CreateObject. Late binding is safer for sharing workbooks, as it doesn’t require the user to change references.

Here’s a simple recursive subroutine that lists all files in a folder and its subfolders into column A of the active sheet:

Sub ListAllFiles(startPath As String)
    Dim fso As Object
    Dim folder As Object
    Dim subFolder As Object
    Dim file As Object
    Dim rowNum As Long
    
    ' Create FileSystemObject (late binding)
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Get the starting folder
    Set folder = fso.GetFolder(startPath)
    
    ' Start writing from row 1
    rowNum = 1
    
    ' Call the recursive helper
    TraverseFolder folder, rowNum
    
    ' Clean up
    Set fso = Nothing
End Sub

Sub TraverseFolder(folder As Object, rowNum As Long)
    Dim subFolder As Object
    Dim file As Object
    
    ' Loop through each file in the current folder
    For Each file In folder.Files
        Cells(rowNum, 1).Value = file.Path
        rowNum = rowNum + 1
    Next file
    
    ' Loop through each subfolder and recurse
    For Each subFolder In folder.SubFolders
        TraverseFolder subFolder, rowNum
    Next subFolder
End Sub

To run it, press Alt+F8, select ListAllFiles, and enter a path like C:\MyDocuments in the input box. The macro writes every file path to column A. Remember to save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.

How to Choose Between Dir and FSO

Your choice depends on your needs:

  • Use Dir if you need a quick, dependency-free loop for a single folder and you’re comfortable with its quirks. It’s faster for simple tasks because it doesn’t require creating objects.
  • Use FSO if you need recursion, access to file properties (size, date, attributes), or you want to avoid string parsing. It’s also easier to read and maintain.

If you use Dir recursively, you must store the subfolder names in a collection or array because you can’t call Dir again while iterating with it. Example of a Dir-based recursive loop:

Sub LoopWithDir(startPath As String)
    Dim subFolders As Collection
    Dim folderName As String
    Dim fileName As String
    
    ' Get all subfolders first
    Set subFolders = New Collection
    folderName = Dir(startPath & "\*", vbDirectory)
    Do While folderName <> ""
        If folderName <> "." And folderName <> ".." Then
            If (GetAttr(startPath & "\" & folderName) And vbDirectory) = vbDirectory Then
                subFolders.Add startPath & "\" & folderName
            End If
        End If
        folderName = Dir
    Loop
    
    ' Process files in current folder
    fileName = Dir(startPath & "\*.*")
    Do While fileName <> ""
        Debug.Print startPath & "\" & fileName
        fileName = Dir
    Loop
    
    ' Recurse into each subfolder
    For Each folderName In subFolders
        LoopWithDir folderName
    Next folderName
End Sub

This code first collects subfolders, then processes files, then recurses. The key is to not call Dir inside a loop that is also using Dir – you must finish one enumeration before starting another.

Common Problems and How to Avoid Them

  • Permission errors – Some folders (e.g., System Volume Information) are inaccessible. Always use On Error Resume Next around file access, or check if the folder exists before recursing.
  • Infinite recursion – Junction points and symbolic links can create loops. FSO’s SubFolders may follow them. Use a visited-path collection to avoid cycles.
  • Dir not resetting – If you call Dir with no arguments after a previous call, it continues the enumeration. To reset, call Dir with an empty string.
  • Path too long – Windows has a 260-character limit for paths. Use the \?\\ prefix or use FSO which handles longer paths if you set the registry key, but this is rare.
  • Hidden/system files – Both methods return them by default. Use File.Attributes to filter if needed.

Filtering Files by Type or Name

Often you don’t want every file, only those with a specific extension or name pattern. With FSO, you can check the file.Name or file.Type property. For example, to process only .xlsx files:

If LCase(fso.GetExtensionName(file.Path)) = "xlsx" Then
    ' process file
End If

With Dir, you can pass a pattern like "*.xlsx" as the second argument, but that only filters the current folder. For recursive filtering, you still need to check each file.

Performance and Best Practices

Looping through thousands of files can be slow if you write to the worksheet inside the loop. Instead, store results in an array or a Collection, then write the array to the sheet in one operation using Range.Resize and .Value = arr. Also, turn off screen updating and calculation with Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual at the start, and restore them at the end.

Example of writing an array at once:

Dim arr() As String
Dim cnt As Long
ReDim arr(1 To 1000)
' ... fill arr ...
Range("A1").Resize(cnt, 1).Value = Application.Transpose(arr)

This reduces the number of worksheet writes from thousands to one, dramatically speeding up the macro.

Frequently Asked Questions

How do I loop through all subfolders recursively in Excel VBA?
Use a recursive subroutine that calls itself for each subfolder. The FSO method shown above is the standard way: get the folder, loop files, then loop subfolders and call the same procedure.

Can I use the Dir function to loop through subfolders?
Yes, but you must first collect all subfolder names into a collection, then process files, then recurse. You cannot call Dir inside a Dir loop because it will overwrite the enumeration.

How do I skip certain folders like hidden or system folders?
Check the folder’s attributes using GetAttr or FSO’s folder.Attributes. For hidden folders, the attribute value is 2 (vbHidden). You can add an If condition to skip them.

Why does my macro stop with “Permission Denied” on some folders?
Some folders are protected by Windows. Use On Error Resume Next before accessing folders or files, and check Err.Number to skip errors gracefully.

How do I loop through folders in a specific order, like alphabetical?
Both methods return folders in an unspecified order. To sort, collect folder names into an array, sort it with a bubble sort or use Application.Sort on a temporary sheet, then iterate the sorted array.

Can I loop through folders on a network drive or SharePoint?
Yes, as long as the path is accessible as a UNC path (e.g., \\server\share\folder). FSO works with network paths, but may be slower. For SharePoint, you may need to map it as a drive or use the web URL with the appropriate library.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.