CODEWITHSUNDEEP
excelvbapassword-protection
edgar piet
edgar piet

Reputation: 67

Open read only workbook for editing

I am trying to automatically update a file on the server that is accessible to everyone as read only. Editing is only for a few people. Therefore, it is password protected, but only for editing and not for opening.

I tried to write a macro to open a password protected file, but that only works for files that are protected against opening. I tried the following code as others suggested it might help to turn of displayalerts and/or ignorereadonly, but that does not help. I keep getting a pop-up that says "Enter password for write access, or open read only". I even tried SendKeys to enter the password, but than the pop-up shows too.

Any suggestions that I might not have though of?

Application.DisplayAlerts = False
Workbooks.Open Filename:="test.xlsx", IgnoreReadOnlyRecommended:=True, Password:="password", ReadOnly:=False

Application.DisplayAlerts = True

Upvotes: 1

Views: 7823

Answers (2)

Patricio Valdebenito
Patricio Valdebenito

Reputation: 11

I have the same problem but this post helped me figure it out. Just add the password for writing to the file. Add the WriteResPassword:="password" ex: Set wb = Workbooks.Open(Filename:=myPath & myFile, Password:="1234abc", WriteResPassword:="1234abc", IgnoreReadOnlyRecommended:=True)

Upvotes: 1

PASUMPON V N
PASUMPON V N

Reputation: 1186

Sub test()

Application.ScreenUpdating = False
Application.DisplayAlerts = False


 Dim wb As Workbook
 Dim fFilename As String

   fFilename = "C:\Documents and Settings\Desktop\test.xlsx"

    SetAttr fFilename, vbNormal

    Set wb = Workbooks.Open(fFilename, Password:="test")

       wb.Worksheets("sheet1").Range("A1").Value = "Hello"
       wb.Close SaveChanges:=True

 ' It will set the File back to Read only   
SetAttr fFilename, vbReadOnly



End Sub

Upvotes: 1

Related Questions