Issues with detecting open file

Question

I have literally copied and pasted and tested every bit of code from may BBs and the same thing happens with all of them. they all either tell me that that the file is open if it's open or closed or they tell me that the file is closed when it is open or closed. The code never gets it correct. Here is the last thing I tried and it was telling me it was not open when it was and when it wasn't

Can someone tell me if this is due to the file being located on the network

Sub Is_WorkBook_Open()

    Dim wBook As Workbook



    On Error Resume Next


    Set wBook = Workbooks("X:\Audit Tracking\Team_Larry\DailyReports\Larry_Blank.xlsm")

    'Not open

    If wBook Is Nothing Then

        MsgBox "Larry's Workbook is not open, Proceed to posting", vbCritical

        Set wBook = Nothing

        On Error GoTo 0

        'It is open
    Else

        MsgBox "Yes it is open, Notify Supervisor to close file", vbInformation

        Set wBook = Nothing

        On Error GoTo 0

    End If

End Sub

Answer 1

it was telling me it was not open when it was and when it wasn't

The Application.Workbooks collection contains all the workbooks opened in this instance of Excel.Application; if the workbook is opened by someone else on another machine, it's not in the collection and you can't use that method to know this.

If you're using the latest & greatest Excel 2016 on Office 365, see how you can dismiss that concern altogether using co-authoring features.

Otherwise, you can try sharing the workbook and then Excel can tell you exactly who has it opened, but then shared workbooks has a number of issues, including but not limited to, the inability to edit VBA code.

Using a hard-coded path is a good way to get false negatives, too. Open the file, verify its actual FullName and use that.

Dim i As Long
For i = 1 To Application.Workbooks.Count
    Debug.Print Application.Workbooks(i).FullName
Next

If the file's location doesn't really matter, only its file name, you can iterate the opened files and see if one has a matching file name:

Dim i As Long
For i = 1 To Application.Workbooks.Count
    If Application.Workbooks(i).Name = "Larry_Blank.xlsm" Then
        MsgBox "File is opened."
        Exit For
    End If
Next

Answer 2

When you open an Excel workbook a hidden temporary copy of the workbook will be created. This is presumably used to recovery crashed files. Notice that the temporary workbook's name and path is the same as the actual workbook but has ~$ prefixed to the filename. Since the file path remains the same, we can assume that the ↓`isWorkbookOpen()↓ will work even with mapped and shared folders.

Function isWorkbookOpen(Path As String) As Boolean
    Dim values() As String
    values = Split(Path, "\")
    values(UBound(values)) = "~$" & values(UBound(values))
    Path = Join(values, "\")

    isWorkbookOpen = Len(Dir(Path, vbHidden)) > 0
End Function

Answer 3

I believe your code will test if you have it open, on the computer your running the code from.

This code will open the workbook, if it opens in a read only state then someone else has it open. Note: If you open it on your computer, and then run this code on the same computer it will report that it's not in a read only state.

Sub Test()
Dim oWB As Workbook

Set oWB = Application.Workbooks.Open("C:\Temp\test.xlsx")      

If oWB.ReadOnly Then
    MsgBox "Open"
Else
    MsgBox "Closed"
End If
oWB.Close

End Sub