Why not just use On Error Resume Next around the Open call?

That suppresses every error the Open call could raise, not just "already open", masking genuine problems like a missing file or a locked network drive. Checking the Workbooks collection first is more precise.

Does this function match on file name or full path?

The function shown matches on workbook name only, since that is what Application.Workbooks exposes. Two different files with the same name in different folders will be treated as the same workbook — a real edge case on shared drives.

How do I activate the workbook if it is already open, instead of doing nothing?

Inside the loop, once you find a matching workbook, call Workbooks(wb.Name).Activate and exit the sub, bringing the already-open file to the front instead of silently skipping it.

VBA Check If Workbook Is Open: Prevent Duplicate-Open Errors

Q: Is there a built-in VBA function for this?

No. VBA has no native IsWorkbookOpen function — the loop-based check shown here is the standard approach every finance macro library uses.

This guide is part of the FinDataPro Excel VBA Workbook Events series.

The IsWorkbookOpen Function

Paste this into a standard module:

Option Explicit

Function IsWorkbookOpen(wbName As String) As Boolean
    Dim wb As Workbook

    For Each wb In Application.Workbooks
        If wb.Name = wbName Then
            IsWorkbookOpen = True
            Exit Function
        End If
    Next wb

    IsWorkbookOpen = False
End Function

Call it before opening anything: If Not IsWorkbookOpen("BankStatement.xlsx") Then Workbooks.Open ...

Download the Example Workbook — Free

The working .xlsm file with the IsWorkbookOpen function already wired up — open it, inspect the code in a standard module, and call it from your own import macro.

Where the Code Goes

Place this function in a standard module so any macro in the workbook can call it. It pairs naturally with Application.GetOpenFilename — check the file is not already open before the picker even opens it, avoiding a locked-file conflict later.

Finance Use Case: Shared Drives, Duplicate Opens

On a shared finance drive, two colleagues running the same import macro within minutes of each other is routine. Without a check, the second run either throws a runtime error or silently opens a second, read-only instance of the same file — and any edits made there vanish on close. The function above stops the macro before that happens, rather than relying on Excel's own file-lock warning to catch it.

Excel's own read-only warning is not a reliable safety net here, because it only fires for the human clicking Open manually — a macro calling Workbooks.Open in code bypasses that dialog entirely and opens a second copy without any prompt at all. Whatever the second colleague enters into that read-only copy simply disappears the moment they close it, with no error to explain why their edits never made it into the shared file. Checking first with IsWorkbookOpen is the only place in the whole macro where that silent data loss can actually be caught.

Activating the Open Copy Instead of Skipping It

Rather than just skipping the open, bring the existing copy to the front:

If IsWorkbookOpen("BankStatement.xlsx") Then
    Workbooks("BankStatement.xlsx").Activate
Else
    Workbooks.Open "C:\Shared\BankStatement.xlsx"
End If

Common Mistakes

Most of the mistakes here come from treating the check as optional overhead rather than the one line standing between a clean import and a silently corrupted shared file.

Relying on On Error Resume Next around the Open call, which suppresses every error type, not just the duplicate-open case.
Matching on full file path when Application.Workbooks only exposes the file name — two same-named files in different folders will be treated as identical.
Forgetting the file extension in the comparison string, causing a false negative every time.
Not exiting the function loop early once a match is found, which wastes cycles on workbooks collections with many open files.

Once the open-check is in place, see GetOpenFilename for a safer file picker, or Close Workbook Without Saving for the corresponding safe-close pattern.

Full reference: Microsoft Learn Workbooks collection reference.

Frequently Asked Questions

Why not just use On Error Resume Next?

It suppresses every error the Open call could raise, not just the duplicate-open case, masking genuine problems too.

Does this match on file name or full path?

Name only, since that is what Application.Workbooks exposes — a real edge case with same-named files in different folders.

Can I bring the open file to the front instead of skipping it?

Yes — call Workbooks(wb.Name).Activate once a match is found.

Is there a built-in function for this?

No — VBA has no native IsWorkbookOpen function. The loop-based check shown here is the standard approach.

Conclusion

A single reusable IsWorkbookOpen function protects every import macro on a shared drive from duplicate-open errors. Drop it into a standard module once, and pair it with GetOpenFilename for a complete, conflict-free import routine.

Prashant Panchal• ACA | FMVA® | 19 Years in Finance

Prashant Panchal is a Chartered Accountant (ACA) and Financial Modelling & Valuation Analyst (FMVA®) with 19 years of experience in finance, FP&A, and financial modelling across the GCC region. He is the founder of FinDataPro.

Discussion

Comments are moderated and appear once approved.