How to open a macro enabled Excel file on a SharePoint in edit mode from an Excel vba?

Question

I have an macro enabled Excel file on a SharePoint that when a user opens it from the SharePoint the file opens programmatically another macro enabled Excel file on the same SharePoint. The file being opened by the vba macro needs to be edited, and must be editable by multiple users at the same time. However I can't get it to even open in edit mode. Using Office 365

I've tried << ActiveWorkbook.LockServerFile >> but always get an error message << Run-time error 1004: Method 'LockServer' of object'_Workbook' failed >>.

The code that I show below is in the Excel file that is opened manually by the user and that opens automatically the other Excel file. The other Excel file when opened works fine (if I remove the LockServerFile command), all it's macro's work fine, but it is open in read only and changes cannot be saved. Again this file should be editable by multiple users simultaneously.

' this code is in the "ThisWorkbook" tab

Sub workbook_open()

Set DB = Workbooks.Open(DBname, 3, False, , , , True)

ActiveWorkbook.LockServerFile ' this is where is crashes

'more code...

End Sub

' Note: DB is declared in a module

Public DB as Workbook Public Const DBname As String = "https://ledvance365.sharepoint.com ... .xlsm"

Answer 1

Looks like << ActiveWorkbook.LockServerFile >> wasn't working because the SharePoint settings was not on "Open Documents in Client Applications by Default"

But once I got the SharePoint owner to change the SharePoint settings to "Open Documents in Client Applications by Default" the << ActiveWorkbook.LockServerFile >> command worked.

Answer 2

Maybe check out the following link to check if the file is already locked. https://www.mrexcel.com/forum/excel-questions/906983-vba-support-checking-if-file-locked-sharepoint.html

Also in general it helps if you use your objects when you set them.

Dim DB as Workbook
Set DB = Workbooks.Open(filename:=DBname, editable:=True)
DB.LockServerFile

Answer 3

I had a similar issue. The code crashes at the same point you highlighted.

ActiveWorkbook.LockServerFile

Seems to fail when the document is already editable.

On Error Resume Next
    ActiveWorkbook.LockServerFile

Fixes the issue I was experiencing since the code will continue on when the file is already editable and similarly makes a file editable if it wasn't previously.