Link to home
Create AccountLog in
Avatar of Rob4077
Rob4077Flag for Australia

asked on

Use VBA to determine if an Excel spreadsheet is locked

I am using the following function to determine if an Excel Worksheet is locked for editing. The spreadsheet is saved on Google Drive and is updated routinely by another user. The function works most of the time part, but if I run the function and it responds indicating the sheet is locked (usually when Google Drive is uploading the file) then it will continue reporting that the sheet is locked until I manually open the spreadsheet with Excel and save it (without making changes). Clearly something is not resetting. Can anyone help me identify what I need to do to make it run reliably?

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long
    On Error Resume Next
    ff = FreeFile()
    Open FileName For Binary Access Read Write Lock Read Write As #1
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
        Case 0:     IsWorkBookOpen = False
        Case 70:    IsWorkBookOpen = True
        Case 75:    IsWorkBookOpen = True 'readonly
        Case Else: Error ErrNo
    End Select
End Function
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Rob4077


Hmmm. I didn't do a very good job of such a small function,  did I?

Thanks for highlighting all the errors