checking to see if a workbook is open vba, crashing if file is open

Question

I've found some code on here to check to see if a workbook is open, but I get a

Run-time Error '70': Permission Denied if the file is open.

I'm not sure how to handle this error or modify the code to deal with this.

Sub Sample()
    Dim Ret

    Ret = IsWorkBookOpen("I:\RJB\Juan's Project\Summary Sheet.xlsm")

    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 'This line is highlighted when the crash occurs
    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

For those interested, here is the original question with the code referenced above: http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open-using-vba — Richard Morgan, Aug 22 '13 at 17:59
The code is dependent on that error occurring in the first place, which is why the line `On Error Resume Next` exists: to suppress that error message so that it can check what the ErrNo is later. Did you change the code at all? — tigeravatar, Aug 22 '13 at 18:02
I don't think so, the code I'm using is above, I only changed the file name to I:\RJB\Juan's Project\Summary Sheet.xlsm. — John Miko, Aug 22 '13 at 18:09
I just tested it, code works fine for me, even with a file on a network drive, both Excel and non-Excel files. — tigeravatar, Aug 22 '13 at 18:13
Do you think it might be something to do with my computer then or some other problem? — John Miko, Aug 22 '13 at 18:16
Maybe an option in your VBA Editor. Open the VBA Editor and go to Tools -> Options -> General -> and in the "Error Trapping" section select "Break on Unhandled Errors" -> OK. Yours might be set to "Break on All Errors". — tigeravatar, Aug 22 '13 at 18:27
Yes! That's was what was happening. Thanks so much tigeravatar! — John Miko, Aug 22 '13 at 18:33

score 2 · Accepted Answer · answered Aug 22 '13 at 19:00

This was originally a comment that was found to be the correct solution to this question, so I am adding it as an answer:

Maybe an option in your VBA Editor. Open the VBA Editor and go to Tools -> Options -> General -> and in the "Error Trapping" section select "Break on Unhandled Errors" -> OK. Yours might be set to "Break on All Errors".

checking to see if a workbook is open vba, crashing if file is open

1 Answers1