How to deal with co-authoring while editing an Excel file in Sharepoint via VBA

Question

I have an excel file stored in Sharepoint (which is also accessible with Microsoft Teams), with the path: https://organization.sharepoint.com/PathOfFile/myFile.xlsx

The file can be edited by multiple at the same time with the co-authoring feature in Sharepoint.

I want to use another excel file stored locally in my computer to access and modify the one in Sharepoint. This local file has a button with this VBA code in it:

Sub UpdateSP():

    f_name = "https://organization.sharepoint.com/PathOfFile/myFile.xlsx"

    Workbooks.Open f_name
    Workbooks("myFile.xlsx").Activate
    ActiveWorkbook.Sheets("sheet1").Activate

    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.Value = 9999
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = 0000

    ActiveWorkbook.Close SaveChanges:=True

End Sub

In principle it works, the file in Sharepoint is modified. But things go wrong if there's someone editing the file while I run the code, then two versions of the file seem to be created, one for the online-live editing, and the one for my code.

If this happens, the online version of the file won't show the changes made by the code, and whenever the file is opened with the excel app, a pop-up will show asking which version of the file should be kept, losing all the changes done in the disposed version.

I have tried to use the CanCheckOut and CheckOut methods, but CanCheckOut always returns False for whatever reason (there are some questions here with the same issue but I havent been able to find a solution).

Can someone suggest a solution to this issue? Thanks.

score 1 · Answer 1 · answered Jul 10 '20 at 02:58

I'm not 100% sure it will work on SharePoint, but in theory, ADODB is a library for VBA that has the syntax of objects to use Microsoft's Jet Engine so you can open files AdLockOptimistic---ally. ((look up lock types in ADO.net))

This works on a file directory basis, so if the DB being modified is open, it will handle the update.

Instead of using Excel's Application to open the file, you would establish an ADO connection, and then specify the type of Lock in order to access the Excel's sheets and tables inside it.

This works for shared / network drives, so I'm guessing since SharePoint can be mapped as a file explorer drive, then ADO should work and is worth a try.

Here's a basic example to get you started: ADO question

score 0 · Answer 2 · answered Jul 14 '20 at 17:36

Try enabling the autosave after activating the workbook.

To do so, add this line: ActiveWorkbook.AutoSaveOn = True

after the Workbooks("myFile.xlsx").Activate line.

I have had similar issues with collaborative files and making sure the autosave is enabled has solved it.

score 0 · Answer 3 · answered Jul 14 '20 at 22:49

To be able to incorporate changes that way your code must run inside a coauthoring context.

Instead of opening the document from another doc or local copy, the code must be running inside the same document being opened from the same source URL (Sharepoint or OneDrive), that way the add-in or macro can make changes that Excel itself will handle on a coauthoring context.

I recommend taking a look at Coauthoring in Excel add-ins of the Office Dev Center, including the linked articles inside (specifically "coauthoring", redirecting to the support center, and "About coauthoring in Excel (VBA)" at the bottom with more samples).

score 0 · Answer 4 · answered Jul 16 '20 at 17:52

CanCheckOut will always return false if a workbook is open. Thus you must check before you touch it. The CheckOut command will not open the file so we must also have an open statement after CheckOut.

Using your example it would look like this;

Option Explicit

Public Sub UpdateSP()
    Dim fName As String
    fName = "https://organization.sharepoint.com/PathOfFile/myFile.xlsx"

    If Workbooks.CanCheckOut(fName) Then
        Workbooks.CheckOut fName
        Dim myFile As Workbook
        Set myFile = Workbooks.Open(fName)
        Dim mySheet As Worksheet
        Set mySheet = myFile.Sheets("Sheet1")
        Dim startRange As Range
        Set startRange = mySheet.Range("A" & mySheet.Rows.Count).End(xlUp).Offset(1)
    
        startRange.Value = 9999
        startRange.Offset(0, 1).Value = 0
    
        myFile.Close SaveChanges:=True
    Else
        MsgBox fName & " can't be checked out at this time.", vbInformation
    End If
End Sub

How to deal with co-authoring while editing an Excel file in Sharepoint via VBA

4 Answers4