List Files and Folders Recursively with Excel VBA
Publication Date:
Updated:
INFORMATION > List Files and Folders Recursively with Excel VBA
Bottom line: Recursively visit each folder, emit the files you need, and pass every child folder back into the same procedure. Add explicit error handling and a policy for junctions, symbolic links, hidden folders, and access-denied paths.
What you'll learn
- How FileSystemObject exposes folders, subfolders, files, names, and paths.
- How recursion builds a complete tree and why deep or cyclic trees need safeguards.
- How to write results to a worksheet without slow cell-by-cell operations at large scale.
Who this is for: Excel users inventorying a controlled Windows directory tree.
2026 context: The sample reflects the author's original local test. Validate permissions and link behavior on a copy of your directory structure, and consider PowerShell or a dedicated inventory tool for very large or security-sensitive trees.
Overview
This article shows how to enumerate files and folders recursively with VBA and covers these tasks:
- I want to get a list of all files or folders under a folder with their full paths.
- I want to process files or folders under a folder in a loop.
Table of Contents
1. Programming Method
The following sample demonstrates the recursive traversal.
The example defines a Function that can be copied as shown and called from a Sub procedure.
'Parameter Examples
'## filePath C:\Users\user\Desktop\test\
'## kind false:folder, true:file
'## list
Function getFolderOrFile(filePath As String, kind As Boolean, list As Collection) As Collection
'delete\
Dim filePathEnd As String
filePathEnd = Right(filePath, 1)
If filePathEnd = "\" Then
filePath = Left(filePath, Len(filePath) - 1)
End If
'Folder existence check
Dim result As String
result = Dir(filePath, vbDirectory)
If result = "" Then
MsgBox "File does not exist!"
Exit Function
End If
If kind Then
Dim buf As String
buf = Dir(filePath & "\*.*")
Do While buf <> ""
list.Add filePath & "\" & buf
buf = Dir()
Loop
Else
list.Add filePath
End If
Dim folder As Object
With CreateObject("Scripting.FileSystemObject")
For Each folder In .GetFolder(filePath).SubFolders
getFolderOrFile folder.Path, kind, list
Next folder
End With
End Function
To use it, pass the following as arguments
- filePath — directory. Example)C:\Users\user\Desktop\test
- kind — Pass true,false. If True, returns a list of files. If False, returns a list of folders.
- list — The collection will be used to store the results. An example implementation is described below.
An example implementation of an actual Function call is shown below.
Sub test()
Dim list As Collection
Set list = New Collection
getFolderOrFile "C:\Users\user\Desktop\test", False, list
Dim item As Variant
For Each item In list
'------TODO------
ThisWorkbook.Worksheets(1).Cells(1, 1).Value = item
Next
End Sub
When getFolderOrFile is called, list contains a list of files or folders. The implementation example is False, so the list of folders is stored.
Describe the processing you want to do in the "TODO" section of the For statement. item can be treated as a String variable.
The reference source will be the process of setting the acquired list to A1 in Excel. (This process overwrites the same cell repeatedly.)
2. Code Description
This is the end of the explanation of how to use the system, followed by an explanation of the code.
The details can be seen in the code, but the key point is that getFolderOrFile is called further in the getFolderOrFile method.
In the process of searching for folders, the process is further repeated by calling the process of searching for folders, and the mechanism is to search all folders.
By calling its own method, it repeatedly calls its own method as long as the folder is found, searching all folders.
3. Conclusion
This pattern lists files and folders recursively in VBA. Add an explicit policy for inaccessible paths and filesystem links before using it on an unfamiliar directory tree.
Official references
■INFORMATION
■PROFILE
■CONTACT