CODEWITHSUNDEEP
excelvbasharepoint
Dennis
Dennis

Reputation: 1

Opening Excel file from Sharepoint as Read-Write

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.

file appears like a normal Read-Only file without the option to Enable Editing

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?

Upvotes: 0

Views: 13692

Answers (3)

Czeskleba
Czeskleba

Reputation: 464

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.

Upvotes: 3

KDxcel
KDxcel

Reputation: 1

I know your query was posted long ago but I have found the solution to remove the Read-Only blocker and update the excel document via Macro:

If you add "ActiveWorkbook.LockServerFile" after the code of opening the file, then it removes the Read-only and updates the excel as normal.

Upvotes: 0

Jose Paredes
Jose Paredes

Reputation: 339

The interaction designed for Excel-OneDrive-SharePoint is new in 2016 apps and that version is a requisite to properly work.

The version 2013 may work by tweaking the OnDrive “Account” settings regarding Office co-authoring configuration which is specifically applied to Excel and Word

Right click the OneDrive icon in the taskbar to reach settings

Good luck!

Upvotes: 0

Related Questions