How to Check If an Excel File Is Open Using Excel VBA

Published

By John Dalesandro

Automating Excel tasks may require checking if a file is already open before running macros. Attempting to modify or overwrite a workbook that is already open can cause errors, prevent changes from being saved as expected, or produce unexpected results.

This article demonstrates two VBA methods to safely check if a workbook is open. One approach checks whether the file is locked or otherwise unavailable for the required access, while another checks whether the workbook is open in an Excel instance.

Why Check If an Excel File Is Open

Opening a workbook that is already open can cause runtime errors or prevent updates from being applied correctly. Checking the file state first can help avoid file-access conflicts and unintended changes.

Checking whether a file is open before working with it provides several benefits:

This small validation step can significantly improve the stability of Excel automation.

There are two different ways to approach this problem. A file-lock test determines whether the file is currently unavailable for read/write access, while a GetObject check determines whether the workbook is open in a running Excel instance. The first approach is broader, while the second provides more direct confirmation that Excel has the workbook open.

Method 1: File-Lock Test VBA Function

The following function checks whether a workbook is open in the current Excel instance or whether the file cannot be opened with the required read/write access and locking.

Option Explicit

Function IsExcelFileOpen(ByVal filePath As String) As Boolean
  Dim wb As Workbook
  Dim fileNumber As Integer
  Dim errNumber As Integer

  ' File must exist to be considered open
  If Len(Dir$(filePath, vbNormal)) = 0 Then
    IsExcelFileOpen = False
    Exit Function
  End If

  ' Check if workbook is open in this Excel instance
  For Each wb In Application.Workbooks
    If StrComp(wb.FullName, filePath, vbTextCompare) = 0 Then
      IsExcelFileOpen = True
      Exit Function
    End If
  Next wb

  ' Check if file is open in another Excel instance or locked
  On Error Resume Next
  fileNumber = FreeFile

  Open filePath For Binary Access Read Write _
       Lock Read Write As #fileNumber

  Close #fileNumber
  errNumber = Err.Number
  On Error GoTo 0

  IsExcelFileOpen = (errNumber <> 0)
End Function

NOTE:

  • filePath should include the full path to the Excel file.
  • The IsExcelFileOpen function returns True if the file is open and False if it is not.
  • Only a simple file existence check is performed before the open-workbook and lock checks. For a more comprehensive file existence check, consider using this alternative Excel VBA function to check if a file exists.

Known Limitations of Method 1: File-Lock Test

The IsExcelFileOpen function is a best-effort approach. Be aware of the following limitations:

This function works well for local or network files in controlled environments but should not be considered fully multi-user safe, especially for cloud or SharePoint files.

For files hosted on SharePoint or OneDrive, checking lock or check-out status requires a different tool. The Microsoft Graph API exposes this through the lockedByOtherApp and checkout properties on a DriveItem, and is the appropriate approach for cloud-hosted files rather than a VBA-only solution.

Method 2: Detecting Open Workbooks With GetObject

The lock-test method used in IsExcelFileOpen detects a file lock regardless of which process holds it, whether that is Excel, a backup utility, or antivirus software. GetObject takes a different approach. It connects directly to a running Excel instance and inspects its Workbooks collection, returning the exact workbook and instance if a match is found.

The IsWorkbookOpenViaGetObject function can be pasted into the same module created earlier.

Option Explicit

Function IsWorkbookOpenViaGetObject(ByVal filePath As String) As Boolean
  Dim xlApp As Object
  Dim wb As Object

  ' Attempt to connect to a running Excel instance
  On Error Resume Next
  Set xlApp = GetObject(, "Excel.Application")
  On Error GoTo 0

  ' No Excel instance is running
  If xlApp Is Nothing Then
    IsWorkbookOpenViaGetObject = False
    Exit Function
  End If

  ' Check if the workbook is open in the connected instance
  For Each wb In xlApp.Workbooks
    If StrComp(wb.FullName, filePath, vbTextCompare) = 0 Then
      IsWorkbookOpenViaGetObject = True
      Exit Function
    End If
  Next wb

  IsWorkbookOpenViaGetObject = False
End Function

Known Limitations of Method 2: GetObject

When multiple Excel instances are running at the same time, GetObject may return an instance other than the one containing the workbook being checked, so a workbook open in a different instance may not be detected. This is a documented limitation of how GetObject resolves running instances through the Running Object Table, and it applies regardless of Windows version. Use the lock-test method in IsExcelFileOpen for a general-purpose open check across any process. Use GetObject when confirming that a specific file is open in Excel itself, and only one Excel instance is expected to be running.

GetObject detects only Excel instances running on the local machine. It cannot detect a workbook open by another user on a network share, unlike the lock-test method, which detects locks regardless of which machine holds them.

How to Install the VBA Functions

  1. From the Developer tab in Excel, click Visual Basic.
  2. In the Visual Basic for Applications window, go to Insert > Module.
  3. Paste the functions into the new module.
  4. Close the editor and return to Excel.

The functions are now available to use in any macro within the workbook.

How to Use the VBA Functions

Here is a simple example that demonstrates how to use each function in a test macro:

Option Explicit

' Test File-Lock Method (IsExcelFileOpen)
Sub TestExcelFileOpen()
  Dim filePath As String

  filePath = "C:\temp\test.xlsx"

  If IsExcelFileOpen(filePath) Then
    MsgBox "The Excel file is already open!"
  Else
    MsgBox "The Excel file is not open."
  End If
End Sub

' Test GetObject Method (IsWorkbookOpenViaGetObject)
Sub TestExcelFileOpenGetObject()
  Dim filePath As String

  filePath = "C:\temp\test.xlsx"

  If IsWorkbookOpenViaGetObject(filePath) Then
    MsgBox "The Excel file is already open!"
  Else
    MsgBox "The Excel file is not open."
  End If
End Sub

Results

By handling these cases, macros can avoid errors and respond more intelligently.

Summary

Checking whether an Excel file is already open is a simple but essential step in reliable VBA automation. The IsExcelFileOpen function uses a lock test to detect a file in use by any process, making it a general-purpose check. The GetObject approach in Method 2 confirms whether a specific workbook is open in a running Excel instance, which is useful when only one instance is expected to be running. Choosing between the two depends on whether the check needs to cover any process holding the file or specifically confirm the workbook is open in Excel. Adding either check to an automation workflow helps prevent errors, protects data, and makes macros behave more predictably across Excel instances.