Is Excel File already open in VBA – By Another User

Is Excel File already Open – Check in VBA?

While opening a Excel file or be it any file with VBA, You may need to check if it is already opened by another user for editing.

The file may present in local computer or a shared drive. In either way, if the file is already open by other user for editing, then the current VBA code cannot edit & save it in same name.

For this purpose, it is a good practice to check if file is already open or not, before processing it.  Here is the code to check this.

VBA Code Check if File Open?

Function isFileOpen(sFileName As String) As Boolean
    Dim iFile As Double
    Dim bReturn As Boolean
    Dim eVal As Double
    bReturn = False
    
    'Open File with Lock - Errors out if file already opened
    On Error Resume Next
    iFile = FreeFile
    Open sFileName For Input Lock Read As #iFile
    Close #iFile
    eVal = VBA.Err
    On Error GoTo 0
    
    'Check error code & return file status
    If eVal <> 0 Then bReturn = True
    isFileOpen = bReturn
End Function

This function returns true if the file is locked for editing.

Detecting Excel File Locked by another User with VBA

The above code will take input as Excel file path or any file path. Try to open it will lock mode. If it is not available for exclusive lock access, then it means the file is already in open state. And hence this will return a True. If it is available for edit, then the function will return a False.

There is also possibility that the file is being edited by someone. The macro without knowing it, it might be editing an incomplete data. To avoid such scenarios, this macro will be helpful.

External Reference: Here is a discussion forum topic on how to find if a Excel workbook is already opened. There are multiple methods discussed in this page. But, You could see that the above method is being approved by a lot & fool proof.