Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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.

BartH_NL_0-1662463420406.png

 

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:

BartH_NL_1-1662463595825.png

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

BartH_NL_0-1662463872795.png

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

@BartH_NL 

Maybe this link will help you further...

SharePoint files open as read-only

@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.

@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.

@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 :)

 

@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:

BartH_NL_0-1662563595377.png

 

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.

BartH_NL_1-1662563595378.png


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

 

 

 

@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 :)