Excel disable Read-Only from SharePoint with VBA

[BartH_NL]

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
        DoEvents
        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)
        DoEvents
        If ActiveWorkbook.AutoSaveOn = True Then ActiveWorkbook.AutoSaveOn = False
        DoEvents

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

[#2]

@BartH_NL 

Maybe this link will help you further...

SharePoint files open as read-only

[#3]

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

[#4]

@BartH_NL 

VBA Code to open an Excel file on a sharepoint site

[#5]

@NikolinoDE 

Thanks again Nikolino,

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

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

Mine:

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.

[#6]

@BartH_NL 

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"
else
ActiveWorkbook.ChangeFileAccessMode:=xlReadOnly
End If
End With
' had yet to be added
end sub

...is also new water for me VBA with Sharepoint 🙂

[#7]

@NikolinoDE 

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 :-(.

[#8]

@BartH_NL 

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.

SharePoint

Your place for SharePoint news, announcements and best practices.

I wish you strength, patience and success with Office 🙂

[#9]

@BartH_NL I’ve been bashing my head against this issue for 2 days and thought I’d share a solution that worked for me. 
After invoking the Excel.Application and workbook to open (with the ReadOnly parameter set to False), execute the following method:

exApp.ActiveWorkbook.LockServerFile()

You will immediately see your visible Excel remove the read only banner and you can merrily save. This method changes the ReadOnly flag from True to False.

Hope this helps.