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
FolderandFileobjects, 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 Nextaround file access, or check if the folder exists before recursing. - Infinite recursion – Junction points and symbolic links can create loops. FSO’s
SubFoldersmay follow them. Use a visited-path collection to avoid cycles. - Dir not resetting – If you call
Dirwith no arguments after a previous call, it continues the enumeration. To reset, callDirwith 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.Attributesto 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
- Excel Template for Family Budget: How to Build One
- Excel Template for HOA Budget: How to Build One
- Excel Template for Event Budget: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Access VBA: How to Delete Table Rows Safely and CorrectlyIn Access VBA, a “row” is usually a record in a table. To delete matching records, run a…
- Best Revolution Macro Settings for RuneScape 3 CombatFor most RuneScape 3 players, the best Revolution setup is a hybrid: let Revolution activate a well-ordered set…
- VBA Dictionary Count: How to Get the Number of KeysTo get the number of keys in a VBA Dictionary, read its Count property: dict.Count. It returns the…
- Excel Macro Goal Seek: Automate a Target-Value SearchAn Excel Goal Seek macro uses VBA’s Range.GoalSeek method to make a formula cell reach a target by…
- Excel VBA Loop Through Rows: Practical Examples and TipsTo loop through rows in Excel VBA, set a row counter and use a For...Next loop, usually from…
- Excel VBA Array to Collection: Methods and ExamplesTo convert a VBA array to a Collection, create a Collection and add each array element in a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.