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

mrr010

New Member
Joined
Apr 27, 2017
Messages
5
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top