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

You should avoid `On Error Resume Next` like the plague. Remove that, test it with every scenario and see what, if any, error is actually returned. Then trap for that specific error. Personally, I would also add code to make sure the file even exists at all. — Bill Hileman, Jul 11 '18 at 16:52
removed the On Error Resume Next and received Runtime error '9' Subscript out of Range on this line — rholdren, Jul 11 '18 at 17:25
Set wBook = Workbooks("X:\Audit Tracking\Team_Larry\DailyReports\Larry_Blank.xlsm") — rholdren, Jul 11 '18 at 17:25
That's because Workbooks is interpreted as an undimensioned array. You're apparently not using the correct syntax to open a workbook. — Bill Hileman, Jul 11 '18 at 17:28
@BillHileman `Workbooks` is an object collection, not an array. Error 9 is thrown when the specified file doesn't exist in the collection, which is exactly by design. OP isn't trying to open anything here. — Mathieu Guindon, Jul 11 '18 at 17:29
Thanks, Mathieu, I don't work with spreadsheets so I did not pick up on that. Makes sense - an error 9 works the same way with collections as arrays. So that's specifically what you should be checking for, @rholdren. — Bill Hileman, Jul 11 '18 at 17:31

Mathieu Guindon · Answer 1 · 2018-07-11T18:03:02.833

0

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

edited Jul 11 '18 at 18:03

answered Jul 11 '18 at 17:34

Mathieu Guindon

69,817
8
107
235

The workbook does exist but when I copy and paste the code from above I'm still getting "Larry's workbook is not open" but it is – rholdren Jul 11 '18 at 17:41
@rholdren is it opened **on another computer**? Take the time to read the top part of this answer. – Mathieu Guindon Jul 11 '18 at 17:41
copied and pasted about and entered the file is open and still tell me it's not – rholdren Jul 11 '18 at 17:48
I have it open on my computer – rholdren Jul 11 '18 at 17:49
Compare the path you're using to `Debug.Print Workbooks(1).FullName` (or `Workbooks(2).FullName`, depending on whether the file was opened first or second). **If someone else has it opened on another machine, you will not be able to know**. Excel will tell you. – Mathieu Guindon Jul 11 '18 at 17:51
@rholdren edited with more ideas... that said I don't know why you'd need a macro to tell you whether you have a given workbook opened on your own machine... you don't know what workbooks you're opening? – Mathieu Guindon Jul 11 '18 at 18:04
I have an audit team who when they post an audit score it goes from the auditor file to a supervisor file. If the supervisor file is open the audit won't post. It was my hope that in the beginning of the posit process I could have the auditor notified that the file was already open that way they would know to send an email to the supervisor to close the file. – rholdren Jul 11 '18 at 18:07
@rholdren if "the auditor" and "the supervisor" are two different persons on two different computers that potentially have the same file-on-the-network opened, then you're trying to determine whether `Application.Workbooks` contains a file that someone else has opened, and for one more time, *you can't do that*. See if [**co-authoring**](https://support.office.com/en-us/article/collaborate-on-excel-workbooks-at-the-same-time-with-co-authoring-7152aa8b-b791-414c-a3bb-3024e46fb104) features can help, or research how to make the workbook a *shared workbook*. – Mathieu Guindon Jul 11 '18 at 18:11

score 0 · Answer 2 · answered Jul 11 '18 at 18:15

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

JosephC · Answer 3 · 2018-07-11T18:46:15.900

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

Issues with detecting open file

3 Answers3