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.