Check if Excel file is being opened as readonly mode by another computer

Question

From this link I've found this code :

Option Explicit

Sub Sample()
    Dim Ret

    Ret = IsWorkBookOpen("C:\myWork.xlsx")

    If Ret = True Then
        MsgBox "File is open"
    Else
        MsgBox "File is Closed"
    End If
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

But for me, the question in that link is not clear. I'm unable to know whether the question is asking about the workbook is already open in the same computer, or if the workbook is already open by another computer where both computers connected to the same network.

I'm imagining something like this :
Computer-A and Computer-B connected to the same network.
Comp-A make a shared folder which has the excel file, say test.xlsm,
so comp-B can open test.xlsm from Comp-A shared folder.

Since I don't have two computers and the network... I wonder if the case something like this :
Comp-B open the test.xlsm as read only mode

While test.xlsm being opened by Comp-B as read only mode,
later on Comp-A open BLA.xlsm which has macro to open test.xlsm,
so it check first with the sub/function above before opening it.

My question :
will Open FileName For Input Lock Read As #ff throw an error ?

why i ask, because I'm not sure if the sub/function above is meant for "comp-B open test.xlsm as normal mode" only, or it also meant for : "comp-B open test.xlsm as readonly mode"

Any kind of response will be greatly appreciated. Thank you in advanced.

Answer 1

The function IsWorkBookOpen() simply checks whether a workbook is already opened by another process. If so, it returns True. Otherwise, it returns False.

When opening a file using the Open statement, specifying the access type as Lock Read is not allowed if the file is already opened by another process. Otherwsie, an error occurs.

Have a look at the following Microsoft reference...

Open Statement

Answer 2

Finally I can borrow a laptop and a network dongle.

So... the laptop is comp-B, and my desktop is comp-A.
comp-A make a shared folder which contains file :

1. WBtest.xlsb
   
2. TestOpenWBtestAsReadOnly.xlsm
   
3. TestIsWorkbookOpened.xlsm

case-X:
First, comp-B open TestOpenWBtestAsReadOnly.xlsm and run a sub which open WBtest.xlsb in readonly mode.

Then comp-A open TestIsWorkbookOpened.xlsm and run the sub/function. The msgbox say : "File is Closed".

In comp-B, I close the WBtest.xlsb, then

case-Y:
in comp-B, I manually open this file by double-clicking it. (So WBtest.xlsb is open in normal mode).

comp-A run the sub/function. . The msgbox say : "File is Open".

I've tried again and again between X and Y, it give me the same result.

My question :
will Open FileName For Input Lock Read As #ff throw an error ?

The answer to my own question :
No, it won't throw an error if the file being opened by another computer is in readonly mode.
Yes, it will throw an error if the file being opened by another computer is in normal mode.

Thank you.