VBA Workbook Open Event: Auto-Run a Macro on File Open

This guide is part of the FinDataPro Excel VBA Workbook Events series — 15 dedicated guides covering event-driven automation for finance workbooks.

What Is the Workbook_Open Event?

Paste this into the ThisWorkbook module (Alt + F11, then double-click ThisWorkbook under Microsoft Excel Objects):

Option Explicit

Private Sub Workbook_Open()
    ' Refresh all data connections the moment the file opens
    On Error GoTo RefreshFailed
    ThisWorkbook.RefreshAll
    Exit Sub

RefreshFailed:
    MsgBox "Could not refresh links. Check the shared folder connection.", vbExclamation
End Sub

Every time this workbook opens — for you or for anyone else with access — RefreshAll runs immediately, before the user sees stale numbers.

Where the Code Goes

Workbook_Open only fires from the ThisWorkbook object. Paste the same code into a standard module (Module1) and it will sit there silently forever — no error, no warning, it simply never runs. This is the single most common reason a “broken” workbook event turns out to have been in the wrong place all along.

Finance Use Case: Force-Refresh a Shared Bank Statement Folder

Shared reconciliation workbooks usually pull from a linked bank statement folder or a Power Query source. If the last person to use the file forgot to refresh before closing, the next person opens stale data and reconciles against numbers that are already wrong. Wiring the refresh into Workbook_Open removes the human step entirely — it happens whether the previous user remembered or not.

This matters most on month-end close files that move between multiple preparers. It is the same problem a GetOpenFilename file picker solves from the other direction — removing a manual step that a user could get wrong.

Common Mistakes

• Pasting the code into a standard module instead of ThisWorkbook — it never fires.
• Saving as .xlsx instead of .xlsm — Excel strips VBA silently on save.
• No error handling — if RefreshAll fails (folder unreachable, VPN down), the workbook opens with a raw runtime error instead of a clear message.
• Forgetting that Trust Center macro settings can block the event from firing for other users entirely.

Combining With Workbook_BeforeClose

Workbook_Open is one half of a standard protect-and-unprotect pattern used across finance templates. Unprotect or refresh on open, then re-protect or validate on close using the Workbook_BeforeClose event. Together they bookend the file's lifecycle so nothing depends on a user remembering a manual step.

Testing Without Closing and Reopening

You do not need to close and reopen the file every time you tweak the code. In the VBA Editor, place the cursor inside the Workbook_Open sub and press F5 to run it directly. This is faster to iterate on than testing the real close/reopen cycle, and it behaves identically since the sub itself does not know how it was triggered.

For the full event reference, see the Microsoft Learn Workbook.Open event reference.

Frequently Asked Questions

Conclusion

The Workbook_Open event turns a manual startup checklist into something that just happens. Paste the code into ThisWorkbook, save as .xlsm, and test with F5 before relying on it. Pair it with Workbook_BeforeClose to bookend the file's lifecycle, and if bank reconciliation is what you are refreshing links for, see what AutoBankRec does with the data once it is refreshed.

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.