• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 44
  • Last Modified:

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
0
Rob4077
Asked:
Rob4077
1 Solution
 
Fabrice LambertFabrice LambertCommented:
Hi,

The "On Error Goto 0" statement reset the err object, regardless an error occured or not.
Instead of checking if an error occured mixed with the function logic, it is better to separate with an error handler:
Public Function IsWorkBookOpen(FileName As String) As Boolean
On Error Goto Error
	 Dim ff As Long
	 
	 ff = FreeFile()
	 
	 Open FileName For Binary Access Read Write Lock Read Write As ff
	 Close ff
		'// no error occured, function return false
	 IsWorkBookOpen = False
Exit Function
Error:		'// Error handler start here
	Select Case Err.Number
	Case 70, 75
		IsWorkBookOpen = True
	Case Else
		err.Raise err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext
	End Select
 End Function 

Open in new window

Couple more mistakes I noticed:
- You declare and initialize a file number, but never use it to open the file, then close the file number. Duno what the behavior is in this case, either undefined or always successfull (thus generating no error, and your function returning false).
- There are no semi-colons in select-case statements
- You don't need to copy the error number in a separate variable.
- If nothing can handle the error, better raise (or escalate) it.
0
 
Rob4077Author Commented:
Hmmm. I didn't do a very good job of such a small function,  did I?

Thanks for highlighting all the errors
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now