Forum Discussion
BartH_NL
Sep 06, 2022Copper Contributor
Excel disable Read-Only from SharePoint with VBA
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 ...
BartH_NL
Sep 07, 2022Copper Contributor
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.
NikolinoDE
Sep 07, 2022Gold Contributor
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 🙂
- BartH_NLSep 07, 2022Copper Contributor
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 :-(.- NikolinoDESep 07, 2022Gold Contributor
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 🙂