🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Check If File Exists — Dir vs FileSystemObject.FileExists (and the Trap Inside a Dir Loop)

|

VBA Check If File Exists — Dir vs FileSystemObject.FileExists (and the Trap Inside a Dir Loop)

TL;DR — Two correct ways, one for each toolset. Dir is the one-liner when you are nowhere near a Dir loop; FileSystemObject.FileExists is the safe default because it has no hidden state:

' Built-in, one line — but shares Dir's single global cursor:
If Dir("C:\Reports\March.xlsx") <> "" Then MsgBox "It's there"

' Stateless, clearer, distinguishes file vs folder — the safer default:
Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\Reports\March.xlsx") Then MsgBox "It's there"

"Does this file exist?" is the single most-searched file question in VBA, because the alternative — opening a file that is not there — raises run-time error 1004 and stops an unattended macro dead. The good news is the check is a one-liner. The catch is that the obvious one-liner, Dir, carries the same hidden cursor that powers a Dir loop — so where you call it matters as much as how.

What you'll learn

  • The wrong reflex — "just open it and trap the error" — and why it is the worst option
  • The two right answers — Dir and FileSystemObject.FileExists
  • The trap that defines this topic — the Dir check resets a Dir loop
  • How Dir mishandles a folder path or a trailing backslash
  • FileExists vs FolderExists — say exactly what you mean
  • Why "check then open" still needs an error guard (the tiny race, and the practical fix)

The wrong reflex: open it and catch the error

The tempting shortcut is to skip the check entirely and let the failure tell you:

On Error Resume Next
Set wb = Workbooks.Open(path)        ' if it's missing, 1004 fires and is swallowed
On Error GoTo 0
If wb Is Nothing Then MsgBox "Not found"

This works, but it is the worst of the three options. It is slow (Excel actually attempts to open the file), it swallows unrelated errors — a corrupt file, a permissions problem, a locked file all look identical to "missing" — and it leans on On Error Resume Next, which hides bugs when left switched on. Testing existence is cheap and precise; opting out of it to lean on an error handler trades a clean answer for a muddy one. Check first.

The two right answers

Dir — the built-in one-liner. With no wildcard, Dir simply tests one path and returns the file name if it is there or "" if it is not:

If Dir("C:\Reports\March.xlsx") <> "" Then   ' "" means not found

Short, no references, no object. Its one liability is the shared cursor (next section).

FileSystemObject.FileExists — the stateless default. A true predicate that returns True/False and touches no global state:

Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\Reports\March.xlsx") Then ...

It reads as what it does, distinguishes files from folders, and — the reason it is the safer default — you can call it anywhere, including in the middle of a Dir loop, without side effects. Use late binding (CreateObject) so it runs on every machine; see VBA FileSystemObject.

The trap that defines this topic: Dir check inside a Dir loop

This is the reason the two checks are not interchangeable, and it is the same hidden-cursor behaviour that governs Dir. Dir keeps one module-wide search position. Calling Dir(path) to test a file starts a new search — wiping the position your enumeration loop was relying on:

name = Dir(folder & "*.xlsx")
Do While name <> ""
    If Dir(folder & "done\" & name) = "" Then    ' ← resets the cursor — loop breaks
        Process folder & name
    End If
    name = Dir()          ' now advances the "done\" search, not the *.xlsx one
Loop

The inner Dir reseeds the engine, so the loop skips files, repeats them, or never ends. The rule is absolute: inside a Dir loop, never call Dir to check another file — use fso.FileExists instead. It has no cursor to disturb. This single interaction is why "which existence check?" is not a matter of taste: if a Dir enumeration is anywhere on the call path, the answer is FileExists.

How Dir mishandles folders and trailing backslashes

Even outside a loop, Dir has two edge cases FileExists avoids because Dir was built to match names, not to answer a clean yes/no about a file:

  • A folder path. Dir("C:\Reports") can return "Reports" — matching the folder — so a "file exists" test passes for something that is a directory. fso.FileExists("C:\Reports") correctly returns False (it is a folder), and fso.FolderExists answers the folder question separately.
  • A trailing backslash. Dir("C:\Reports\") tests the folder's existence, not a file inside it. Easy to hit when you build paths by concatenation and leave a stray separator.

By default Dir also skips hidden and system files, so a file that genuinely exists but is hidden reports as missing unless you pass the vbHidden attribute. FileExists has none of these ambiguities — it answers "is there a file at exactly this path," full stop.

Check then open: the small race, the practical fix

One honest caveat: between the instant you check and the instant you open, another process could delete or lock the file. In practice this "time-of-check to time-of-use" gap is tiny and rarely matters — but the correct pattern is not to choose between the check and the error guard, it is to use both:

If Not fso.FileExists(path) Then
    MsgBox "File not found: " & path       ' the expected, common case — a clean message
    Exit Sub
End If
On Error GoTo OpenFailed                    ' the rare case — locked, corrupt, permissions
Set wb = Workbooks.Open(path)

The existence check handles the ordinary "it is not there" case with a clear message instead of a crash; the error handler catches the genuinely exceptional failures the check cannot predict. That is the robust shape for any Workbooks.Open in an automated job.

A close cousin of "does the file exist" is "is it already open in Excel," and it is a different question with a different answer — looping Workbooks by name, not touching the disk:

Function IsOpen(fileName As String) As Boolean
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(fileName)     ' by file name, e.g. "March.xlsx"
    On Error GoTo 0
    IsOpen = Not wb Is Nothing
End Function

Existence is about the disk; "already open" is about the in-memory Workbooks collection. Opening a file that is already open does not reload it — Excel just activates the copy in memory — so a batch macro that might run twice should test this before calling Workbooks.Open.

The honest verdict: which check, when

  • Default to fso.FileExists. It is stateless, reads clearly, distinguishes file from folder, and — the decisive point — is the only safe check if a Dir loop is anywhere in play.
  • Use the Dir one-liner only for a quick, isolated test where no Dir enumeration is running and you want zero setup.
  • Never replace the check with a bare open-and-trap; it is slower and hides real errors.
  • Pair the check with an error guard around the open — the check for the common case, the handler for the rare one.

Get this right and the number-one file crash — a 1004 on a path that is not there — becomes a clean, one-line message your unattended macro can log and move past.

How ExcelMaster helps

The existence check looks trivial until you hit the parts that are not — the Dir cursor that a stray check resets, the folder path that a file test wrongly matches, the race that means you still need an error guard. Getting all of it right for every file a batch macro touches is a lot of careful plumbing.

ExcelMaster handles it. Describe the job — "for each expected report, if the file is there import it, otherwise log it as missing and carry on" — and it writes fso.FileExists checks that never disturb your folder loop, pairs each Workbooks.Open with an error handler, and tests whether a workbook is already open before reopening it. You describe the outcome; it makes the checks correct and side-effect-free.

Frequently asked questions

How do I check if a file exists in VBA?

Two good ways. The built-in one-liner is If Dir("C:\path\file.xlsx") <> "" Then — Dir returns the name if the file exists and "" if not. The clearer, stateless way is CreateObject("Scripting.FileSystemObject").FileExists(path), which returns True/False and can be called anywhere. Prefer FileExists, and never test with Dir inside a Dir loop.

Why does my Dir file check break my Dir loop?

Because Dir keeps a single hidden search cursor. Calling Dir(otherPath) to test a file starts a new search and wipes the position your enumeration loop depends on, so the loop then skips files, repeats them, or runs forever. Inside a Dir loop, use fso.FileExists to check other files — it has no shared state.

Should I use Dir or FileSystemObject to check a file exists?

Use FileSystemObject.FileExists as your default — it is stateless, distinguishes files from folders, and is safe to call inside a Dir loop. Use the Dir one-liner only for a quick, isolated check where no Dir enumeration is running and you want no setup. See VBA FileSystemObject.

Does Dir return true for a folder?

It can. Dir("C:\Reports") may return "Reports", matching the folder, so a naive "file exists" test passes for a directory. And Dir("C:\Reports\") with a trailing backslash tests the folder, not a file inside it. FileSystemObject.FileExists avoids both by answering strictly about a file, with FolderExists for the folder question.

How do I check if a workbook is already open in VBA?

That is a different question — it is about memory, not disk. Loop the Workbooks collection by file name: Set wb = Workbooks("March.xlsx") inside an On Error Resume Next block, then test Not wb Is Nothing. Opening an already-open file does not reload it, so a macro that might run twice should check this before calling Workbooks.Open.

Tested in

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

Related guides: VBA Dir · VBA FileSystemObject · VBA Open Workbook · VBA On Error · VBA Close Workbook