Excel VBA to List File Names in All Subfolders Automatically

To list file names from a folder and all its subfolders in Excel for Microsoft 365 on Windows, run a VBA macro that starts at a chosen folder, visits each nested folder, and writes each file’s name and full path to a worksheet. The macro below does that, including files in the starting folder; save a copy of your workbook before running it because VBA changes cannot be undone with Ctrl+Z.

What the macro returns

The result is a worksheet named File List with one row per file and three columns: the file name, its full path, and the folder containing it. For example, if you select C:\Projects, a file in C:\Projects\Reports\2026 appears in the list along with files directly inside C:\Projects. The list is a snapshot: it does not update automatically when files are added, renamed, or removed.

This approach is useful for building an inventory, checking what a project folder contains, preparing a list to review in Excel, or finding files across several levels of folders. It lists files, not the folders themselves. It also does not open, copy, move, or modify the listed files.

VBA code to scan the folders

Paste this macro into a standard VBA module. It uses the Windows FileSystemObject through late binding, so you do not need to add a reference manually. The folder picker lets you choose the starting location when you run the macro.

Option Explicit

Sub ListFilesInAllSubfolders()
    Dim fso As Object
    Dim folderPicker As FileDialog
    Dim ws As Worksheet
    Dim currentFolder As Object
    Dim fileItem As Object
    Dim subFolder As Object
    Dim foldersToVisit As Collection
    Dim rootPath As String
    Dim folderPath As String
    Dim nextRow As Long

    ' Ask which folder to scan.
    Set folderPicker = Application.FileDialog(msoFileDialogFolderPicker)
    With folderPicker
        .Title = "Choose the starting folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        rootPath = .SelectedItems(1)
    End With

    ' Reuse the output sheet or create it if it does not exist.
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("File List")
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = "File List"
    Else
        ws.Cells.ClearContents
    End If

    ' Treat output as text so unusual file names are not read as formulas.
    ws.Columns("A:C").NumberFormat = "@"
    ws.Cells(1, 1).Value = "File name"
    ws.Cells(1, 2).Value = "Full path"
    ws.Cells(1, 3).Value = "Containing folder"
    nextRow = 2

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set foldersToVisit = New Collection
    foldersToVisit.Add rootPath

    ' Remove each folder from the queue, list its files, and queue its children.
    Do While foldersToVisit.Count > 0
        folderPath = CStr(foldersToVisit(1))
        foldersToVisit.Remove 1

        On Error GoTo SkipFolder
        Set currentFolder = fso.GetFolder(folderPath)

        For Each fileItem In currentFolder.Files
            ws.Cells(nextRow, 1).Value = fileItem.Name
            ws.Cells(nextRow, 2).Value = fileItem.Path
            ws.Cells(nextRow, 3).Value = currentFolder.Path
            nextRow = nextRow + 1
        Next fileItem

        For Each subFolder In currentFolder.SubFolders
            foldersToVisit.Add subFolder.Path
        Next subFolder

        On Error GoTo 0
ContinueLoop:
    Loop

    ws.Rows(1).Font.Bold = True
    ws.Columns("A:C").AutoFit
    MsgBox CStr(nextRow - 2) & " file(s) listed on the File List sheet.", vbInformation
    Exit Sub

SkipFolder:
    ' Skip a folder that cannot be read, then continue with the queue.
    Err.Clear
    Resume ContinueLoop
End Sub

The HTML entities shown for comparison and ampersand symbols in the code represent the VBA operators <> and &. When pasting into the VBA editor, use the literal operators: the folder-picker test should read If .Show <> -1 Then Exit Sub, and the message should join text with &. If copying directly from a rendered page, check that those symbols appear as operators in the editor.

How to add and run the macro

Save a copy of the workbook first. Choose an .xlsm file type if you need to retain the macro in the workbook.
In desktop Excel for Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, the VBA editor is available, but keyboard shortcuts and Windows-specific file-system support differ; this Windows macro is not a dependable Mac solution.
In the editor, choose Insert > Module, paste the code into the module, and close or switch away from the editor.
Run the macro using Alt+F8, select ListFilesInAllSubfolders, and choose Run. Pick the top-level folder in the dialog.
Review the File List worksheet. If that sheet already existed, its cell contents were cleared before the new inventory was written.

On Mac, the Visual Basic Editor can generally be opened with Option+F11 (on some keyboards, use Fn as well). However, this macro relies on Windows’ Scripting FileSystemObject and the Windows folder-picker implementation. For a Mac workflow, use a Mac-compatible script or a file-listing tool rather than assuming this exact code will run unchanged. Excel for the web cannot create or run VBA macros; use desktop Excel.

How the folder traversal works

The macro keeps a queue of folder paths in a VBA Collection. It puts the selected folder in the queue, removes the next path, lists that folder’s files, and adds each child folder to the queue. It repeats until no folder paths remain. This queue-based method avoids relying on a separate manually written procedure for each nesting level, so a folder three, ten, or more levels deep is included as long as Excel can access it.

Both the selected folder and its descendants are scanned. If you want only files located in subfolders and not files directly inside the selected folder, the starting folder needs to be used only to add its child folders to the queue; do not add rootPath itself. That change is useful when the selected directory is merely a container and its own files should be excluded.

Folder order is not guaranteed to be alphabetical, and the macro does not sort the output. File-system enumeration order can vary. After the scan, sort the result in Excel if a particular order matters. The full path is included because two different folders can contain files with the same name.

Customize the output for a particular inventory

The three output columns make the results easier to filter and verify. You can adapt the macro for common inventory needs, but make one change at a time and preserve the original workbook.

List only a file name: Column A already contains the name including its extension, such as budget.xlsx. The extension is part of the file name.
Add file extensions separately: Use fileItem.Name for the complete name or fso.GetExtensionName(fileItem.Name) for just the extension, then add a header and output column.
Include file size or modified date: The FileSystemObject exposes properties such as fileItem.Size and fileItem.DateLastModified. Add matching headers and write those properties to additional columns. Size is reported in bytes.
Filter by extension: Add an If test around the lines that write each file. For example, compare LCase$(fso.GetExtensionName(fileItem.Name)) with a lowercase extension such as "pdf". This filters the output, though the macro still visits the folders.
Change the destination: Replace the worksheet name File List consistently if you want another output sheet name. The current macro clears existing cell contents on that sheet, so do not point it at a sheet containing work you need to keep.

Common problems and practical limits

If the macro does not appear in the macro list, confirm the code is in a standard module and the workbook is saved in a macro-enabled format. If Excel blocks macros, follow your organization’s approved security process; do not enable macros in an unfamiliar workbook just to make this one run. A folder picker cancellation exits without scanning.

The code skips a folder if an access or file-system error occurs while processing it. This helps a scan continue when a location is unavailable, but it does not create an error log, so the output may be incomplete. Check permissions and network availability if expected files are missing. Files stored online or in cloud-synced locations may be represented by placeholders or may not be locally accessible, depending on the sync settings.

An Excel worksheet has a maximum of 1,048,576 rows, including the header. A scan with more than 1,048,575 files cannot fit on one sheet. Large inventories can also take time because the macro writes each file to the worksheet separately. For a very large directory tree, consider writing to a text or CSV file, or using a purpose-built inventory tool.

The macro does not deliberately follow shortcuts as if they were folders, and it does not calculate folder sizes. It lists files visible through the folder objects it visits. Avoid scanning system or application directories unless you have a reason: they may contain protected locations, many files, or items that change while the scan is running.

VBA compared with other ways to list files

VBA is convenient when the list needs to land in a workbook and the process will be repeated by Excel users. It can add columns and apply worksheet formatting in the same routine. Its drawbacks are that macros require desktop Excel, may be restricted by security policy, and need maintenance if you want advanced filtering or logging.

Power Query is another Excel option for importing file metadata and refreshing a query. In Windows desktop Excel, the folder connector is available through Data > Get Data > From File > From Folder; available command labels and connectors can differ by Excel build and platform. Power Query is often a better fit for repeatable data transformations, but make sure the selected folder source and the query’s file-inclusion behavior match the inventory you need.

For a one-time list, a file manager or a command-line script may be quicker than maintaining a macro. For a workbook-based, repeatable inventory with custom columns, VBA can be a straightforward choice. Excel for the web does not run VBA, so a web-only process needs a different tool.

Frequently Asked Questions

Does this macro include files in nested subfolders?
Yes. It scans the selected folder and keeps adding child folders to its queue, so it includes files at every accessible depth below the starting folder.

Can I list only file names without their paths?
Yes. Keep the output in column A and remove the lines that write columns B and C if you do not need the full path or containing folder. The file name in column A includes its extension.

Why are some files missing from the list?
The macro can skip a folder that Excel cannot read, such as a restricted or unavailable network location. It also lists only files visible through the folders it successfully visits. Check access permissions, sync status, and whether the selected starting folder is correct.

Will the list update when files change?
No. Running the macro creates a new snapshot and clears the existing contents of the File List sheet before writing fresh results. Run it again to rebuild the list after files change.

Can I run this VBA in Excel for the web or on a Mac?
Excel for the web does not run VBA. The supplied code is written for Excel on Windows and uses Windows file-system components, so it should not be treated as a cross-platform Mac macro. Use a compatible alternative on those platforms.

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.