Excel vba to check if a sharepoint file is in use/editable

[mrr010]

I have some files in company sharepoint network. I am using a local excel vba script to open and edit those files.

Set wb = Workbooks.Open("http://XXXX/XXXXX.xlsm")
wb.LockServerFile

The above code works fine when the file is available (file not in used by other colleagues). When the file is in use, it brings a popup saying:

File In Use
This file is locked for editing by XXXXX
Do you want to:
-View a read-only copy
-Save and edit a copy of the file
-Receive a notification when the server file is available

I am looking for a way to check if the file is editable before opening the file and before running wb.LockServerFile. Something like this:

If FileEditable("http://XXXX/XXXXX.xlsm") = True Then
Set wb = Workbooks.Open("http://XXXX/XXXXX.xlsm")
wb.LockServerFile
Else
'Log the filename, do something else, try again later...
End If

I have tried Workbooks.CanCheckOut("http://XXXX/XXXXX.xlsm") but it always return True. I have tried Open FileName For Binary Access Read Write Lock Read Write As #1 but the Open statement returns runtime error 52 for bad filename. I believe it was due to special characters since my filepath is a network address.

 

[mrr010]

Workbooks.CanCheckOut("http://XXXX/XXXXX.xlsm") returns True
but
Workbooks.CheckOut ("http://XXXX/XXXXX.xlsm") still popup the File In Use dialog

 

[mrr010]

Need advice