VBA & Macro Problems

How to Use VBA to Check if an Excel Workbook is Already Open

Emma Brown Oct 10, 2026 869 views

Question details

The user needs to use VBA to programmatically check whether an Excel inventory workbook is already open or locked by another user.

How to Use VBA to Check if an Excel Workbook is Already Open

Product: Excel
Device & OS: not provided
Scenario: Validating file availability before a point-of-sale transaction proceeds to ensure data can be written without generating system errors.
Observed behavior: The goal is to detect the file status in the background and display a custom message asking the user to wait or retry, rather than allowing the script to fail with a default error.

Before you start

Ensure you have the exact directory path of the target workbook and that your spreadsheet application has macros enabled in the Trust Center settings.

Solution 1Recommended

Test File Lock Using a Binary Read Lock in VBA

This is the most reliable method to check if a workbook is open by attempting to read it with an exclusive lock via VBA.

By trying to open the file with a binary read lock, the system will throw a specific error if the file is already occupied by another user. We can capture this error to determine the file's status.

Open the VBA Editor

Press ALT + F11 in your active Excel workbook to open the Visual Basic for Applications Editor, then click 'Insert' > 'Module' to create a new script area.

Write the File Validation Function

Create a custom function that accepts a file path string. Within this function, use the 'Open' statement to attempt opening the file: 'Open FilePath For Binary Access Read Lock Read As #FileNum'.

Add Error Handling

Place 'On Error Resume Next' before the Open statement. Check if 'Err.Number' is greater than 0. If it is, the file is locked or open elsewhere.

Close the File and Reset Error Catching

Use 'Close #FileNum' to release the file number, and 'On Error GoTo 0' to reset the error handler so it doesn't affect the rest of your macro.

Integrate with the Main Macro

Call this function inside your point-of-sale transaction script. If the function returns True (file is open), use 'MsgBox' to ask the user to wait and provide a retry loop.

Test File Lock Using a Binary Read Lock in VBA

Network Latency: When checking files stored on a shared network drive or SharePoint, allow a slight delay or implement a short retry loop, as network latency can temporarily affect the file lock status.

Advanced Spreadsheet Management

Write and Run VBA Macros Seamlessly with WPS Office

WPS Spreadsheet fully supports VBA macros, allowing you to easily write, edit, and troubleshoot your point-of-sale scripts with an interface similar to Microsoft Excel.

1. Install WPS Office: Download and install WPS Office, then open your macro-enabled workbook (.xlsm).
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon menu.
3. Open the VBA Editor: Click on 'Visual Basic' or press ALT + F11 to launch the VBA environment.
4. Implement Your Code: Paste your binary read lock script into the module and save your changes.
5. Test the Macro: Run the macro directly within WPS Spreadsheet to ensure it correctly identifies open or locked files.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) macro formatsRobust built-in VBA editor for writing and testing scriptsLightweight architecture that opens large inventory workbooks instantlyFree to use with comprehensive data processing functionalities

microsoft office alternative - wps office

Frequently Asked Questions

Can I check if a workbook is open without actually opening it in Excel?

Yes, by using the binary read lock method in VBA, you only interact with the file at the system level. You do not actually load the workbook into the Excel application interface.

What is the specific VBA error code for an already open file?

When using the binary access method, attempting to lock an already open file typically returns Err.Number 70, which signifies 'Permission Denied'.

How do I check if a workbook is set to read-only before opening it?

You can use the GetAttr function in your VBA code combined with the vbReadOnly constant (e.g., GetAttr(FilePath) And vbReadOnly) to verify the file attributes before attempting any edits.

Excel & Data Problems