troubleshooting Question

Use VBA to determine if an Excel spreadsheet is locked

Avatar of Rob4077
Rob4077Flag for Australia asked on
VBAMicrosoft ExcelMicrosoft Office
2 Comments1 Solution67 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros