Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Save Excel file with a write protect password? 1

Status
Not open for further replies.

RobSchultz

Programmer
Jun 1, 2000
444
US
Is it possible to save a write-protect password (not write reserved or read password) with a workbook using VBA? I have a write protected file I need to open, edit and save and keep the write protect password intact. I don't want any Excel prompts for passwords during the entire procedure.

TIA,
Rob

Rob

-Focus on the solution to the problem, not the obstacles in the way.-
 
From VBA help, these are the arguments of the Saveas function:

SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

so Thisworkbook.saveas fielname:=thisworkbook.path & thisworkbook.name, writerespassword:="password"

should work just fine

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Geoff,

Thank you for the reply. However, I have tried the WriteResPassword to no avail. I've also tried .WritePassword and .ChangeFileAccess (as well as all three together). I continuously receive a 1004 error (password not correct) but I *know* I have the correct password because test files with something as simple as "a" as the password fail.

Here is the scenario:
I am trying to bypass all Excel prompts so I want to feed the workbook the read and write passwords. I have gotten around the read password and now need to tackle feeding it the write password.
I have a write protected file with a known password. I want to open the file in read-only mode (otherwise the write password prompt appears), edit the file, then save the file to a different location while giving it back the same write password (and read password).

Regards,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-
 
Here's some code that might address your problem:

Protecting / Unprotecting a sheet [27/10/2001]
The macros below will protect/unprotect the current worksheet with a password.
Sub ProtectSheet()
Dim Password 'This line of code is optional
Password = "1234"
ActiveSheet.Protect Password, True, True, True
End Sub
Sub UnProtectSheet()
Password = "1234"
ActiveSheet.Unprotect Password
End Sub

Protecting all sheets [27/10/2001]
To protect all the sheets this macro uses all the methods contained in this page (see counting sheets). The If, Then statement is also used here. This tests for a condition and if the condition is TRUE, then the macro continuous the next line of code. In this case it will END the macro. If the condition is NOT TRUE, then it will go to the following line which in this case is to select the next sheet. You will also notice the For, Next statement is also used. This acts as a counter to tell the macro how many loops to run. In this case if there are 3 sheets, the macro will run 3 times protecting all the 3 sheets.

Sub protectAll()
Dim myCount 'This line of code is optional
Dim i 'This line of code is optional
myCount = Application.Sheets.Count
Sheets(1).Select 'This line of code selects the 1st sheet
For i = 1 To myCount
ActiveSheet.Protect
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Protecting your VB code [10/3/2002]
To protect your VB code from being seen by others, all you need to do is go to the project explorer, point at your project and right click on it. Select VBA project properties, click on the protection tab and check the Lock project for viewing and key your password. That's it.

Hope this helps.
 
Thanks to all that replied to my question.

I was doing it correctly from the get-go by passing it the WriteResPassword on the .SaveAs method (just as xlbo suggested). Unfortunately, it was failing because I was attempting to overwrite the original file and/or overwrite an existing copy of the file. Once I wrote it out as a *new* file it works fine. Once I started with a clean slate and methodically went through each step the problem was obvious.

Thanks again,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-
 
aaaaah - glad you got it solved AND posted why it wasn't working. Thank you for that [cheers]

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top