Excel disable Read-Only from SharePoint with VBA

Copper Contributor

Hello Group,

We have a lot of Excel files that are saved with "read-Only recommended".


I would like to open files with VBA and also capture the click on "Edit Anyway" in code.



I have this code:



        Dim exApp As Excel.Application
        Dim wb As Excel.Workbook
        Set exApp = Excel.Application
        exApp.Visible = True
'        Set wb = App.Workbooks.Open(Filename:=sfilename, UpdateLinks:=False, ReadOnly:=False, IgnoreReadOnlyRecommended:=true)
        Set wb = exApp.Workbooks.Open(Filename:=sfilename, ReadOnly:=False)
        If ActiveWorkbook.AutoSaveOn = True Then ActiveWorkbook.AutoSaveOn = False



When I open the file from SharePoint, there is no Read-Only active:


When I open it with the VBA code, Read-Only is activated,


although I have ReadOnly:=False in my Workbooks.Open statement.

How can I make this behave the way I want (so the file is opened in Excel without Read-Only restriction, same as when "Edit Anyway" would have been clicked).
Grtz, Bart

7 Replies


Maybe this link will help you further...

SharePoint files open as read-only

Thank you Nikolino, but the problem doesn't exist when I open the file (manually) from SharePoint, but when I open it with VBA.


Thanks again Nikolino,

In the code which Vit has found, is the same statement I have used:

Set wb = xlApp.Workbooks.Open(xlFile, , False)


Set wb = exApp.Workbooks.Open(Filename:=sfilename, ReadOnly:=False)

But alas, it still results in a Read-Only opened file.
ReadOnly:=False is simply ignored.


Try this code and see if it works

Sub write protectionONOFF()
With ActiveWorkbook
If .ReadOnly = True Then
'direct entry of the password
ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite, WritePassword:="mypass"
End If
End With
' had yet to be added
end sub

...is also new water for me VBA with Sharepoint :)



Well Nikolino,
Then I suspect I have just made it your problem too ;);)

The suggestion you made, doesn't apply as there is no Read-Only password. The file was saved with "Read-Only recommend" on:



Now for the fun part: even when I store the file without "Read-Only recommend" on (so it's off), the file is opened as read-Only from VBA, even though this is the open code:


Workbooks.Open ReadOnly:=False, IgnoreReadOnlyRecommended:=True, UpdateLinks:=False, Filename:=sfilename


And for some more fun: now when I resave the file with "Read-Only recommend on" and open the file from SharePoint, there isn't the "Edit Anyway" button anymore.


So once again, Microsoft has managed to leave me speechless, helpless and clueless :-(.





Unfortunately, I can't help, although I suspect that it could not necessarily be Office. But I don't have a sharepoint to try to start it. Here is a link that might help.

Why did my file open read-only?


Try it here too...maybe I'm the one who can't suggest a simple solution and not microsoft.


Your place for SharePoint news, announcements and best practices.


I wish you strength, patience and success with Office :)