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.
