Opening Excel file from Sharepoint as Read-Write

Question

I automatically open, edit, save and close several Excel workbooks from a Sharepoint location. The following code opens the workbooks (path loops through a list to hit each workbook name):

Workbooks.Open Filename:=path, ReadOnly:=False, Editable:=True

The files open in Read-Only mode, and the yellow dialogue option to Enable Editing does not appear.

I edit these workbooks manually and through a macro, but I am unable to save the files back onto the Sharepoint afterwards without saving as a new file.

I am using Excel 2013. This was working as intended about a year ago, but I believe there may have been updates to Office 365. I checked all of the Excel workbook security options, and nothing is set to open by default as Read-Only.

Is there any way to open the file in an editable mode through the macro, or at the very least allow the Enable Editing option to appear for each workbook?

You may have to uncheck all the boxes on the protected view setting in the trust center. Or you may have to allow Documents on a network to be trusted in your Trusted Documents section of Excel. I may also be the sharepoint security that is causing the "read-only". — mooseman

Czeskleba Czeskleba · Accepted Answer · 2020-01-29T09:11:29

I have been trying to fix the same problem for my files, and eventually did! So I felt that I could maybe let others know. And this old-ish thread came up near the top of my google search.

What fixed it for me was to edit the link.

from: https://Company.sharepoint.com/:x:/r/teams/TeamNo/Shared%20Documents/Example/CoolFolder/TheBestExcelFile.xlsm

To: https://Company.sharepoint.com/teams/TeamNo/Shared%20Documents/Example/CoolFolder/TheBestExcelFile.xlsm

Note that I only used replace to get rid of :x:/r/. I feel like I should have noticed this before but I didn't and no amount of meddling with the Workbook.Open parameters got me anywhere. It just seems odd that the default link copy thing gives you one with special commands in it. For our company most folders have spaces so the link has tons of "%20" in there so I simply read over the ":x:/r/".

Hope it helps someone.

Just for clarity, try this:

Sub Example()

'1.) Get filepath from somewhere
FilePath = Replace("https://Company.sharepoint.com/:x:/r/teams/TeamNo/Shared%20Documents/Example/CoolFolder/TheBestExcelFile.xlsm", ":x:/r/", "")

'2.) Open the file
Set StatisticsFile = Workbooks.Open(FileName:=FilePath, Password:="123")

'3.) Do things

'4.) Close the Sheet, save the changes. I simply like it this way, could be done in a single line.
StatisticsFile.Save
StatisticsFile.Close savechanges:=False

End Sub

I noticed this solution because I could still save the .xlsx file manually with the same name if I navigated to SaveAs. So if you guys can still do that after opening the file via macro, try a similar solution.

Opening Excel file from Sharepoint as Read-Write

3 Answers