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

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.

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

         

         

         

Resources