View original (French)

VBA: Allow modifications on open

Solved
BretonBeurre Posted messages 12 Status Member -  
fabien25000 Posted messages 697 Status Member -
Hello everyone,

I want to open a password-protected file using a macro in VBA. Moreover, I also activated the prompt asking the user for confirmation if they want to make changes to the file:

"The author wants you to open "FileName" as read-only, unless you need to make changes. Do you want to open it as read-only?"

I wrote the following code to open the file with the password, but I get stuck at the read-only message. How can I indicate in my code that no, I do not want to open the file as read-only, in order to make changes (copy/paste data)?

Code: 
Sub openFile()
Workbooks.Open Filename:="MyFile", WriteResPassword:="Password"
End Sub

I tried using "Application.DisplayAlerts = False" but it didn't work, the file opens but in read-only mode. Any other ideas?

Thank you.

Configuration: Windows / Edge 17.17134

2 answers

  1.
    BretonBeurre Posted messages 12 Status Member 1
     
    Thank you to fabien25000 for finding the solution, here is the code to use when opening the file: 
    Set wkb = Workbooks.Open(Filename:=sFileName, UpdateLinks:=False, ReadOnly:=True, WriteResPassword:="Password", IgnoreReadOnlyRecommended:=True)

    Thanks again to him!
    1
  2. fabien25000 Posted messages 697 Status Member 59
     
    Hello,
    I don't have time to test, but maybe starting with a msgbox asking if modifications are needed or not, if yes readonly:=true, if not false

    --
    When you only have a hammer, all problems resemble a nail.
    0
    1. BretonBeurre Posted messages 12 Status Member 1
       
      The thing is, you always need to be able to manually open the file, with the option to open it as Read Only or not.
      So I tried to create a message box upon opening, but you can't change a file's attributes when it's open. So I had to write code that, depending on the user's response, closes the file, then reopens it while changing its attribute. Except that when reopening it, it asks me again if I want it Read Only or not… Even with "Application.DisplayAlerts = False".... Too bad ;)
      0
    2. BretonBeurre Posted messages 12 Status Member 1
       
      Unless I've made a mistake in my code, maybe there's another way for it to work than what I've done, but at first glance it doesn't work...
      0
    3. fabien25000 Posted messages 697 Status Member 59
       
      yes, that's not wrong...
      and if you code on 
      Workbook_BeforeSave
      with an if statement and in the condition your 
      environ("username")
      and if it's not you 
      thisworkbook.close(false)
      then you no longer need read-only mode?
      0
    4. BretonBeurre Posted messages 12 Status Member 1
       
      By VBA code, I need to be able to modify and save "MonFichier.xlsx". But manually, I need to specify upon opening if I want to be in read-only mode or not. So I didn't quite understand exactly what you wanted to do, but in the end, my code should end by telling it to save the changes. Thanks anyway for the ideas! ;)
      0
    5. fabien25000 Posted messages 697 Status Member 59
       
      I found this code: 
      Set wkb = Workbooks.Open(Filename:=sFileName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

      to see if it can help you by digging in this direction...
      0