Batch Change Password Protected Excel Worksheets

Topic

New Reply

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Reply | Quote

Replies

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Reply | Quote

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Reply | Quote

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Reply | Quote

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Reply | Quote

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Reply | Quote

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Reply | Quote

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Reply | Quote

So you want to loop through the 6000 workbooks (are they all in the same folder?)
Opening each in turn and looping through each individual workbook, protecting each sheet with a password.
The same password for each sheet in every book?

Reply | Quote

So you want to loop through the 6000 workbooks (are they all in the same folder?)
Opening each in turn and looping through each individual workbook, protecting each sheet with a password.
The same password for each sheet in every book?

Reply | Quote

So you want to loop through the 6000 workbooks (are they all in the same folder?)
Opening each in turn and looping through each individual workbook, protecting each sheet with a password.
The same password for each sheet in every book?

Reply | Quote

So you want to loop through the 6000 workbooks (are they all in the same folder?)
Opening each in turn and looping through each individual workbook, protecting each sheet with a password.
The same password for each sheet in every book?

Reply | Quote

So you want to loop through the 6000 workbooks (are they all in the same folder?)
Opening each in turn and looping through each individual workbook, protecting each sheet with a password.
The same password for each sheet in every book?

Reply | Quote

So you want to loop through the 6000 workbooks (are they all in the same folder?)
Opening each in turn and looping through each individual workbook, protecting each sheet with a password.
The same password for each sheet in every book?

Reply | Quote

So you want to loop through the 6000 workbooks (are they all in the same folder?)
Opening each in turn and looping through each individual workbook, protecting each sheet with a password.
The same password for each sheet in every book?

Reply | Quote

So you want to loop through the 6000 workbooks (are they all in the same folder?)
Opening each in turn and looping through each individual workbook, protecting each sheet with a password.
The same password for each sheet in every book?

Reply | Quote

Yes Catharine, that is exactly what I want to do and they are all in the same folder fortunately although the passwords can be one of two different PWs.

Any suggestions greatly appreciated.

Reply | Quote

Yes Catharine, that is exactly what I want to do and they are all in the same folder fortunately although the passwords can be one of two different PWs.

Any suggestions greatly appreciated.

Hi Leigh

The much lauded free ASAP UTILITIES has a facility to batch change passwords in one WB, running it twice with each password shuld do it. As for multiple WB’s I have no idea but perhaps you could look contact the author as he answers questions on his web blog.

HTH

Reply | Quote

How about something like this:
[codebox]Option Explicit
Sub ChangeAllPasswords()
Dim strPath As String
Dim strFile As String
Dim sNewPW As String
Dim sPW1 As String
Dim sPW2 As String
Dim wkb As Workbook
Dim wks As Worksheet

‘Change as desired
sPW1 = “pw1”
sPW2 = “pw2”
sNewPW = “NewPW”
strPath = “c:MyPath”

On Error GoTo ErrHandler
Application.ScreenUpdating = False

strFile = Dir(strPath & “*.xls”)
Do While strFile “”
Set wkb = Workbooks.Open _
(Filename:=strPath & “” & strFile)

For Each wks In wkb.Worksheets
With wks
If .ProtectContents = True Then
On Error Resume Next
.Unprotect (sPW1)
.Unprotect (sPW2)
On Error GoTo ErrHandler
.Protect (sNewPW)
End If
End With
Next
wkb.Close (True)
strFile = Dir
Loop

MsgBox “Done”

ExitHandler:
Set wks = Nothing
Set wkb = Nothing
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub[/codebox]

It opens each XLS workbook in the folder you define, and loops thru the worksheets. If worksheet is currently unprotected, it stays that way. If a worksheet has either of 2 passwords, it changes the password to a new one. If it has a different password it remains that password.

Steve

Reply | Quote

My apologies to all that have replied so far – I actually meant workbooks are protected. Some have one password and others another. But there are still >5000 of them and although I appreciate the code posted by sdckapr, I am unable to make it look at each workbook and then apply one of the two passwords.

And yes, ASAP Utilities would be great (for the worksheets example) but I got it wrong, Steve, I actually meant workbooks need to be looped through.

So I would appreciate more assistance (edit: and or suggestions) please.

Reply | Quote

What type of Workbook password? Is it a password to open and/or edit the file (option to set when saving the file), or the workbook password you set while in excel?

You mention that the workbooks have 1-5 worksheets. Is this relevant and how? What do you want done exactly…

If you were going to do this manually describe what you would do for the first 2 files in the folder…

Steve

Reply | Quote

What type of Workbook password? Is it a password to open and/or edit the file (option to set when saving the file), or the workbook password you set while in excel? Yes, the password is to open the file.

You mention that the workbooks have 1-5 worksheets. Is this relevant and how? What do you want done exactly… Sorry, this was because I was thinking of worksheets when I first posed the question.

If you were going to do this manually describe what you would do for the first 2 files in the folder… Open the first, apply the password, save the file (Save As) with the password now being blank (“”), Close the file and open the next, apply password, save the file without password and close it.

Thanks for your reply Steve; does this make it clearer?

From what I can see I need to consider the folder contents as a collection or array for VBA purposes, but I don’t know how to do that.

Reply | Quote

Does this work?
It tries to open each file with the first or second password. If the file is opened, it is saved with no password. If the file has a different password to open, the file is unaffected.

Steve

[codebox]Option Explicit
Sub RemovePasswordToOpen()
Dim strPath As String
Dim strFile As String
Dim sPW1 As String
Dim sPW2 As String
Dim wkb As Workbook

‘Change as desired
sPW1 = “pw1”
sPW2 = “pw2”
strPath = “c:MyPath”

On Error GoTo ErrHandler
Application.ScreenUpdating = False

strFile = Dir(strPath & “*.xls”)
Do While strFile “”
Set wkb = Nothing
On Error Resume Next
Set wkb = Workbooks.Open _
(Filename:=strPath & “” & strFile, _
Password:=sPW1)
Set wkb = Workbooks.Open _
(Filename:=strPath & “” & strFile, _
Password:=sPW2)

On Error GoTo ErrHandler
If Not wkb Is Nothing Then
Application.DisplayAlerts = False
wkb.SaveAs Filename:=strPath & “” & strFile, _
Password:=””
Application.DisplayAlerts = True
wkb.Close (True)
End If
strFile = Dir
Loop

MsgBox “Done”

ExitHandler:
Set wkb = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub[/codebox]

Reply | Quote

Regrettably Steve I got an Automation Error (-2147221080 stopping at wkb.SaveAs FileName:=strPath & “” & strFile, Password:=””) which I can’t find on Microsoft site.
Below is the code with my variations. Do you see any problems with it?

[Codebox]
Sub RemovePasswordToOpen()
Dim strPath As String
Dim strFile As String
Dim sPW1 As String
Dim sPW2 As String
Dim wkb As Workbook

‘Change as desired
sPW1 = “secret”
sPW2 = “secret01”
strPath = “C:Test”

On Error GoTo ErrHandler
Application.ScreenUpdating = False

strFile = Dir(strPath & “*.xls”)
Do While strFile “”
Set wkb = Nothing
On Error Resume Next
‘ Set wkb = Workbooks.Open(FileName:=strPath & “” & strFile, Password:=sPW1)
Set wkb = Workbooks.Open(FileName:=strPath & “” & strFile, Password:=sPW2)

On Error GoTo ErrHandler
If Not wkb Is Nothing Then
Application.DisplayAlerts = False
wkb.SaveAs FileName:=strPath & “” & strFile, Password:=””
Application.DisplayAlerts = True
wkb.Close (True)
End If
strFile = Dir
Loop

MsgBox “Done”

ExitHandler:
Set wkb = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub

ErrHandler:
MsgBox “The error number is ” & Err & “. Its message text is: ” & Error(Err)
Resume ExitHandler
End Sub
[/Codebox]

Reply | Quote

Hi Steve – I have a workaround. I simply rem’d the second Open command (the one with Password:= sPW2) and ran the macro. It unprotected all the files in my test sample (6) that matched password sPW1.

Then I changed the REM to the second password and re-ran the macro and cleaned up the others.

However, in an attempt to make the code more efficient I have been unable to modify your code so that the macro can try both passwords before removing the correct password. Any suggestions?

Reply | Quote