Excel 2016 sharepoint file still opens as readonly

2
votes

Since updating to Office 2016 I can't get excel to open a sharepoint file as editable, despite declaring it to do so.

Workbooks.Open ThisWorkbook.Sheets("Filelist").Cells(i, 2), _
    UpdateLinks:=False, ReadOnly:=False, Local:=True, Editable:=True

The file opens without issues, but I have to run a break on the next line to stop the macro and manually select EDIT, before allowing the code to proceed.

enter image description here

Whilst this is an obvious work around, I am looping through about 40 files, and have to do this manually in each open instance.

2
excelsharepointvba
I'm hoping someone can find a solution to this; I have a similar issue (files on SharePoint), and this is just a nuisance when you're 30+ files in. The only work-around suggested to me is a VBA script to make all open files editable, but not upon open. I haven't tried to build that, yet... – Cyril
@Cyril I have found a VBA approach, check my answer. – Tim Wilkinson

2 Answers

5
votes

Ok so I found a solution to this LockServerFile is the equivalent of hitting the Edit Workbook button.

When opening with VBA you can follow the open command with:

Workbooks.Open ThisWorkbook.Sheets("Filelist").Cells(i, 2)
ActiveWorkbook.LockServerFile

Solved my problem for now if anyone comes across a similar issue.

0
votes

ActiveWorkbook.LockServerFile

The above code will lock that workbook for editing. You can edit the workbook and you can save it. But when you try to open the workbook again by manually, the changes you had done will not reflect in that sheet.