VBA Dir in Excel — Loop Through Files in a Folder, and the Stateful Iterator That Bites You

TL;DR — Dir is not a plain function. The first call passes a path and gets the first matching file name; every later call passes nothing and gets the next name, until it returns "". So the loop looks like this — and you must never call Dir again inside it:

Sub ListWorkbooks()
    Dim folder As String, name As String
    folder = "C:\Reports\"
    name = Dir(folder & "*.xlsx")      ' first call: seed the search
    Do While name <> ""
        Debug.Print folder & name      ' Dir returns the NAME only — prepend the folder
        name = Dir()                   ' no argument: the NEXT match
    Loop
End Sub

The moment your macro has to process many files — consolidate twelve regional workbooks, import every CSV in a drop folder — you reach for Dir. It is the built-in that needs no references and no setup. But it hides a design that trips almost everyone: Dir remembers where it was, globally, and a second search silently wipes that memory. Understand that one fact and every "it skipped half my files" bug disappears.

What you'll learn

The mental model — Dir is a stateful iterator disguised as a function
The one rule that trips everyone — never call Dir again in the middle of a Dir loop
Why Dir returns the file name only, and you must prepend the folder yourself
The wildcard (*.xlsx) and how Dir handles no match ("", not an error)
Why Dir cannot recurse into subfolders — and what to use instead
Why you should collect names into an array first, then open/move/delete the files

The mental model: Dir is an iterator, not a lookup

Most functions give the same answer every time you call them with the same input. Dir does not. It keeps a single, hidden, module-wide cursor into one folder search:

Dir("C:\Reports\*.xlsx") — the argument starts a new search and returns the first match.
Dir() — no argument advances the same search and returns the next match.
When there is nothing left, Dir() returns an empty string "".

Think of it as a cursor you seed once and then step forward. The Do While name <> "" loop above is the canonical shape: seed with the path, then call the argument-less Dir() at the bottom of each pass. That is the whole pattern, and it is the shape you should memorise.

The rule that trips everyone: don't call Dir inside a Dir loop

Because there is only one hidden cursor, calling Dir with an argument anywhere inside the loop starts a brand-new search and throws away your place. This is the number-one Dir bug, and it is subtle because it looks completely reasonable:

name = Dir(folder & "*.xlsx")
Do While name <> ""
    If Dir(folder & "flag.txt") <> "" Then   ' ← DISASTER: resets the cursor
        Process folder & name
    End If
    name = Dir()          ' now this continues the "flag.txt" search, not the *.xlsx one
Loop

The inner Dir(folder & "flag.txt") restarts the engine on a different pattern. When the loop then calls Dir(), it advances that search — so you either process the wrong files, skip most of your .xlsx files, or spin forever. The rule is absolute: inside a Dir loop, the only Dir you may call is the argument-less Dir(). If you need to test for another file mid-loop, use FileSystemObject.FileExists instead — it has no shared cursor to corrupt.

Dir returns the name, not the path

Dir hands back the bare file name — March.xlsx, not C:\Reports\March.xlsx. Forgetting this is the second most common mistake, because the next thing you do is usually open the file:

Set wb = Workbooks.Open(name)             ' error 1004 — "March.xlsx" is not a full path
Set wb = Workbooks.Open(folder & name)    ' correct — rebuild the full path

Always keep the folder string separate and concatenate it back on when you need the full path. This is also why the folder variable ends in a backslash ("C:\Reports\") — so folder & name is a valid path with no missing separator.

The wildcard and the "no match" case

The argument to the first Dir is a path plus a pattern. * matches any run of characters, ? matches a single character:

Dir("C:\Reports\*.xlsx") — every .xlsx in the folder
Dir("C:\Reports\Q?-2026.xlsx") — Q1-2026.xlsx, Q2-2026.xlsx, …
Dir("C:\Reports\March.xlsx") — no wildcard, so it just tests one file: returns "March.xlsx" if it exists, "" if it does not (this is the one-liner behind checking whether a file exists)

If nothing matches, Dir returns "" — it does not raise an error. That is why Do While name <> "" handles both "no files at all" and "reached the end" with the same test.

One trap: by default Dir also matches folders and skips hidden/system files. If a subfolder happens to match your pattern you can get its name back too. When it matters, pass the vbNormal attribute explicitly or verify with the FileSystemObject.

Dir cannot recurse — and modifying the folder mid-loop corrupts it

Two hard limits push you off Dir for anything beyond a flat list:

It is not recursive. Dir walks one folder. There is no argument to descend into subfolders, and you cannot nest two Dir loops (one cursor, remember). To process a tree — C:\Reports\2026\Q1\..., Q2\... — you need the FileSystemObject, whose SubFolders collection is built for recursion.

Changing the folder while looping breaks the enumeration. If you Kill, rename, move, or even Open and SaveAs files during a Dir loop, you are mutating the very folder the hidden cursor is walking. Names get skipped or repeated. The fix is a two-pass pattern — collect first, act second:

Dim names() As String, n As Long
ReDim names(1 To 1000)
Dim f As String: f = Dir(folder & "*.xlsx")
Do While f <> ""                 ' pass 1: just gather the names
    n = n + 1: names(n) = f
    f = Dir()
Loop
Dim i As Long
For i = 1 To n                   ' pass 2: now it's safe to open / move / delete
    Process folder & names(i)
Next i

Once the names are in an array, the fragile cursor is done and you can do whatever you like to the files.

The honest verdict: what Dir is actually for

Dir is the right tool for exactly one job — a quick, flat loop over the files in a single folder, with no references to add and no object to create. For "run this macro on every .xlsx in this folder," it is shorter and faster than anything else. Keep it to that, and follow four rules:

Seed once, advance with Dir() — the only Dir inside the loop takes no argument.
Prepend the folder — Dir returns names; folder & name rebuilds the path.
Two passes when you mutate — collect names into an array, then open/move/delete.
Reach for the FileSystemObject the moment you need subfolders, file size or date, or a second existence check mid-loop.

The instant you find yourself wanting a second Dir in the same loop, that is the signal you have outgrown Dir — not a bug to patch, but a tool to switch.

How ExcelMaster helps

Getting a file loop right means seeding Dir once, advancing with the argument-less call, rebuilding full paths, and knowing when the folder mutation forces a two-pass array — a surprising amount of ceremony for "just loop the files in a folder," and one stray Dir call quietly breaks the whole thing.

ExcelMaster writes the loop for you. Describe the job — "open every workbook in this folder, pull the totals, and close each one" — and it produces a correct Dir loop (or a FileSystemObject walk when you need subfolders), with the folder concatenation, the two-pass array when files are moved, and a matching wb.Close every iteration. You describe the outcome; it handles the iterator's memory so it never bites you.

Frequently asked questions

How do I loop through all files in a folder with VBA?

Seed Dir with a path and wildcard, then advance with the argument-less Dir(): name = Dir("C:\Reports\*.xlsx"), then Do While name <> "" … name = Dir() … Loop. Dir returns each file name in turn and "" when the folder is exhausted. Remember that Dir returns the name only, so use folder & name when you need the full path.

Why does my VBA Dir loop skip files or loop forever?

Almost always because you called Dir with an argument inside the loop. Dir keeps a single hidden cursor, and any Dir(somePath) starts a new search that wipes your place — so the bottom-of-loop Dir() then advances the wrong search. Inside a Dir loop, only ever call the argument-less Dir(). To test for another file mid-loop, use FileSystemObject.FileExists, which has no shared state.

Does VBA Dir search subfolders?

No. Dir walks a single folder and has no recursion, and you cannot nest two Dir loops because there is only one cursor. To walk a folder tree, use the FileSystemObject and iterate its SubFolders collection recursively.

How do I get the full path from VBA Dir?

Dir returns the bare file name (March.xlsx), not the full path. Keep the folder in its own variable (ending in a backslash) and concatenate: folder & name gives C:\Reports\March.xlsx. Passing the bare name to Workbooks.Open raises run-time error 1004.

Should I use Dir or FileSystemObject to loop files?

Use Dir for a quick, flat loop over one folder with no setup. Switch to the FileSystemObject when you need to recurse into subfolders, read each file's size or modified date, or check for other files while you loop — anything where Dir's single hidden cursor gets in your way.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-21.