Forum Discussion

BartH_NL's avatar
BartH_NL
Copper Contributor
Sep 06, 2022

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 ...
excel
Macros and VBA
BartH_NL's avatar
BartH_NL
Copper Contributor
Sep 07, 2022

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.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 07, 2022

    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 🙂

     

    • BartH_NL's avatar
      BartH_NL
      Copper Contributor
      Sep 07, 2022

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

       

       

       

      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Sep 07, 2022

        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 🙂

Resources