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.
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.
