VBA Loop Through Files in a Folder: Examples and Tips

To loop through files in a folder in Excel VBA, use the Dir function: give it a folder path and a wildcard on the first call, then call Dir() repeatedly until it returns an empty string. The example below writes each file’s full path to a worksheet; change the folder and sheet name to suit your workbook.

A basic VBA loop with Dir

This macro reads files directly inside C:\Reports and lists their full paths in column A of Sheet1. It starts at row 2 so the first row can hold a heading.

Sub ListFilesInFolder()
    Dim folderPath As String
    Dim fileName As String
    Dim ws As Worksheet
    Dim nextRow As Long

    ' Set a folder path that ends with a backslash.
    folderPath = "C:\Reports\"
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ws.Cells(1, 1).Value = "File path"
    nextRow = 2

    ' The first Dir call starts the search; later calls get the next match.
    fileName = Dir(folderPath & "*", vbNormal)
    Do While Len(fileName) > 0
        ws.Cells(nextRow, 1).Value = folderPath & fileName
        nextRow = nextRow + 1
        fileName = Dir()
    Loop
End Sub

Replace C:\Reports\ with the folder you want to inspect, and make sure the folder exists. In the Visual Basic Editor, insert the code into a standard module, then run ListFilesInFolder. In Excel for Windows, open the editor with Alt+F11; on Mac, the shortcut is Option+F11, though some keyboards require Fn as well. You can also open the editor from Excel’s Developer tab.

The macro writes names and paths; it does not open, change, or delete the files. If you adapt it to modify workbook contents, save a separate copy of the workbook before running the macro. VBA macros cannot be undone with Ctrl+Z.

How Dir finds the next file

The first call supplies a search expression: the folder path followed by a wildcard. Here, * means any file name, and vbNormal asks for files with normal attributes. The returned value is just the matching file name, not its folder, which is why the example joins the name to folderPath when writing the full path.

Each subsequent call to Dir() advances the same search. When no matches remain, it returns an empty string, so the Do While loop stops. This pattern is useful for many jobs: listing files, importing data, checking file names, or applying the same operation to a collection of workbooks.

Use a trailing backslash in a Windows folder path, such as C:\Reports\, when joining it directly to a file name.
Use Dir(folderPath & "*.csv", vbNormal) to search for CSV files only.
Use the file name to build a full path before passing a file to another procedure.

Dir is not a general-purpose collection object: it keeps track of the current search internally. Avoid starting a second Dir search before finishing the first one. A nested search can replace the active search state, causing the outer loop to skip files or stop unexpectedly. If you need nested folder traversal, use a separate object-based approach or collect the first search’s results before starting another.

Filter for file extensions

A wildcard is the simplest way to choose one extension. For example, starting the search with *.xlsx returns Excel workbooks with that extension. If you need several extensions in one pass, search for all names and inspect each name inside the loop. This standalone example accepts both .xlsx and .xlsm files:

Sub ListExcelFiles()
    Dim folderPath As String
    Dim fileName As String
    Dim extension As String
    Dim ws As Worksheet
    Dim nextRow As Long

    ' Change this folder and confirm that Sheet1 exists.
    folderPath = "C:\Reports\"
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Cells(1, 1).Value = "Excel file"
    nextRow = 2

    fileName = Dir(folderPath & "*", vbNormal)
    Do While Len(fileName) > 0
        If Len(fileName) >= 5 Then
            extension = LCase$(Right$(fileName, 5))
            If extension = ".xlsx" Or extension = ".xlsm" Then
                ws.Cells(nextRow, 1).Value = folderPath & fileName
                nextRow = nextRow + 1
            End If
        End If
        fileName = Dir()
    Loop
End Sub

The extension check converts the last five characters to lowercase, so names ending in .XLSX or .XLSM are accepted too. It does not accept older .xls files, because those extensions are only four characters and are not included in the comparison. Add a separate check if your folder contains that format. Filtering by the name also makes it easy to include files such as .csv or .txt without changing the main loop.

Include files in subfolders

The basic Dir example searches one folder level only; it does not automatically descend into child folders. For a directory tree, the FileSystemObject (FSO) exposes a folder’s files and its subfolders. The following late-bound example recursively lists every file under the starting folder, so it does not require adding a reference in the VBA editor:

Sub ListFilesIncludingSubfolders()
    Dim fso As Object
    Dim root As Object
    Dim ws As Worksheet
    Dim nextRow As Long

    ' Late binding avoids setting a reference manually.
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set root = fso.GetFolder("C:\Reports")
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ws.Cells(1, 1).Value = "File path"
    nextRow = 2
    WalkFolder root, ws, nextRow
End Sub

Private Sub WalkFolder(ByVal folder As Object, _
                       ByVal ws As Worksheet, _
                       ByRef nextRow As Long)
    Dim file As Object
    Dim childFolder As Object

    ' Record files in this folder before visiting its children.
    For Each file In folder.Files
        ws.Cells(nextRow, 1).Value = file.Path
        nextRow = nextRow + 1
    Next file

    For Each childFolder In folder.SubFolders
        WalkFolder childFolder, ws, nextRow
    Next childFolder
End Sub

Keep the two procedures in the same standard module. The recursive procedure visits the current folder’s files and then calls itself for each child folder. FSO provides full paths through each file object, so the code does not need to concatenate folder names. This can take longer than a single-folder search when the directory tree is large, and a protected or disconnected location can cause an access error. If that is possible, add error handling designed for your situation rather than assuming every subfolder is readable.

Dir or FileSystemObject?

For a simple list of files in one known folder, Dir is usually the shortest solution. It uses built-in VBA functionality and has little setup. It is a good fit when you want to match a wildcard, process files one at a time, and do not need detailed folder objects.

FSO is more convenient when you need folder properties, file properties, or a recursive walk through subfolders. It lets you work with objects such as folders and files instead of managing path strings yourself. Late binding with CreateObject avoids a manual reference setting, but it does not make inaccessible folders readable; operating-system permissions still apply.

Choose Dir for a compact, single-folder loop or a simple extension pattern.
Choose FSO when the task needs subfolders or properties such as a file’s size and date.
Do not mix a second Dir search into an active Dir loop; use FSO or a separate collection of names for nested work.

Paths, output, and common problems

A path is a frequent source of errors. In Windows VBA string literals, a backslash is written as a normal character, not as an escape character, so "C:\Reports\" is a valid string. The final backslash matters in the first example because the code appends the returned file name. If you keep the folder path without that slash, add one when constructing the search path and full file path.

Other issues to check include:

Wrong folder: Confirm the spelling, drive letter, and access permissions. A mapped drive may not be available in a different Windows sign-in context.
Wrong worksheet name: ThisWorkbook.Worksheets("Sheet1") refers to the workbook containing the macro, not necessarily the workbook currently selected. Change the name or use a deliberate workbook reference.
No matches: An empty result may mean the folder has no matching files, the wildcard is too narrow, or the path is incorrect.
Hidden or system files: The example uses vbNormal, so it is intended for ordinary files. If you need files with other attributes, choose and test suitable Dir attribute constants rather than assuming the default search includes everything.
Existing worksheet content: The example writes starting at row 2 and does not clear old results. Old rows below the new list can remain, so clear the intended output area carefully if a fresh list is required.

For a very large folder, writing one cell at a time can be slower than building an array and writing it to the sheet in one operation. For ordinary lists, the direct approach is easier to understand and maintain. If the macro is intended to open each workbook, also plan for damaged files, password prompts, read-only files, and workbooks already open in Excel instead of assuming every match will open cleanly.

Running the macro safely and platform notes

VBA runs in desktop Excel, not Excel for the web. On Windows, save a macro-enabled workbook as .xlsm if you want to keep the code in that file. Excel may block macros based on its security settings or the file’s source. Enable macros only for files and code you trust; do not change global security settings just to run an unfamiliar macro.

The examples use Windows-style paths. Excel for Mac supports VBA, but folder paths and file access can differ; use a path appropriate to macOS and check it on the target Mac. The Dir approach is generally the simpler starting point for cross-platform VBA, while Windows-specific assumptions and FSO behavior should be checked before relying on them on Mac. Excel for the web cannot run these macros, so a desktop version is needed to execute them.

Before using a macro on important data, test it on a copy and a small sample folder. Listing paths is a low-impact task, but later changes—such as opening, renaming, moving, or deleting files—can have consequences outside the workbook. Remember that Excel’s undo history does not reverse VBA actions.

Frequently Asked Questions

How do I loop through only CSV files in a folder?
Use Dir(folderPath & "*.csv", vbNormal) for the first search, then use Dir() for each next match. Keep the loop condition checking whether the returned name has zero length.

Does Dir include files in subfolders?
No. A standard Dir search looks in the folder specified by its search path, not throughout the directory tree. Use FSO recursion, or write a separate traversal that explicitly visits each child folder.

Why does Dir return an empty string?
It returns an empty string when the search has no more matches. If it is empty on the first call, check that the folder exists, that the path is correct, and that the wildcard matches the files you expect.

Can I open each file as the loop finds it?
Yes. Use the returned name together with the folder path to construct a full path, then pass that path to the appropriate opening method. Add error handling and close files you open; one corrupt or locked file should not be allowed to derail an important batch process.

Can I run this macro in Excel for the web?
No. Excel for the web does not execute VBA macros. Run the workbook in desktop Excel for Windows or Mac, with any platform-specific path adjustments required.

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.