We have a number of worksheets containing sensitive data that are protected
by password. Periodically, the firm's principal requests that we change the
password for each. As there are a relatively large number of sheets to
change, is there a means by which we could update the password for all sheets
in a folder & its subfolders?

Thank you.
Sprinks

Sprinks, are you saying you want to change the passwords of all the
worksheets in all the workbooks in a folder and its subfolder? How
complicated this will be depends on 1. Do all the worksheets in each
workbook have the same password? 2. Do all the worksheets in ALL the
workbooks have the same password? James
Sprinks wrote:
We have a number of worksheets containing sensitive data that are protected
by password. Periodically, the firm's principal requests that we change the
password for each. As there are a relatively large number of sheets to
change, is there a means by which we could update the password for all sheets
in a folder & its subfolders?

Thank you.
Sprinks

Zone,

Thank you for your response. Im glad you asked the question, because our
requirements are somewhat more complex than I originally thought. The
current situation is:

€¢ Some workbooks have a password but their worksheets do not.
€¢ Some workbooks have a password and passwords on their sheets as well.
€¢ The passwords are currently not all the same due to poor management by
their previous owner. There is no need for them to be different, nor is
there a need to have a password for any worksheets. One on the workbook
itself is sufficient.
€¢ Some workbooks in the current folder do not have a password.
€¢ The VBA solution should:
o request the folder to operate upon
o request the current password from the user
o change the password of all workbooks in the folder and its
subfolders where the currently assigned password matches the one entered or
is blank
o remove the password from all worksheets

The user could initially run the code multiple times for the 2 or 3
passwords currently in use.

The user has asked for one additional requirement. Since she uses dozens,
even hundreds of workbooks a day, she is asking if in lieu of logging into
each workbook with the same identical password, she could log in once into a
€œmaster€� workbook.

As long as this workbook is open, it would provide the password needed to
open the second workbook, providing in essence a €œpassword by session€�. When
she leaves her desk, she will log out of the master workbook to prevent
others from loading any of the protected workbooks.

Thanks for your help.
Sprinks




"Zone" wrote:

Sprinks, are you saying you want to change the passwords of all the
worksheets in all the workbooks in a folder and its subfolder? How
complicated this will be depends on 1. Do all the worksheets in each
workbook have the same password? 2. Do all the worksheets in ALL the
workbooks have the same password? James
Sprinks wrote:
We have a number of worksheets containing sensitive data that are protected
by password. Periodically, the firm's principal requests that we change the
password for each. As there are a relatively large number of sheets to
change, is there a means by which we could update the password for all sheets
in a folder & its subfolders?

Thank you.
Sprinks

What you are after is certianly doable, but you need to break it down into
steps.

0. Ask the user for the new PW to use on all WBs.

1. Open/list files in a folder and subfolders:
See Bob's answer to "Recursive procedure" in this NG from 18/10.

2. Provide an InputBox to accept the user's input of the WB Password.

3. Using PW to attempt to open the first WBs from #1. Trap the error on
failure and optionally record the WB's name.

4. Check for protection on the WSs in the newly opened WB, asking the user
for PW input if necessary. Unprotect each.

5. Protect the WB with the PW from #0

6. Save/Close the WB

7. Loop to #3, until all WBs from #1 are processed.

As for the "session" password, maybe you need application level events, with
an InputBox routine in Personal.xls to request PW upon opening:
http://www.cpearson.com/excel/AppEvent.htm

NickHK

"Sprinks" wrote in message
...
Zone,

Thank you for your response. I'm glad you asked the question, because our
requirements are somewhat more complex than I originally thought. The
current situation is:

. Some workbooks have a password but their worksheets do not.
. Some workbooks have a password and passwords on their sheets as well.
. The passwords are currently not all the same due to poor management by
their previous owner. There is no need for them to be different, nor is
there a need to have a password for any worksheets. One on the workbook
itself is sufficient.
. Some workbooks in the current folder do not have a password.
. The VBA solution should:
o request the folder to operate upon
o request the current password from the user
o change the password of all workbooks in the folder and its
subfolders where the currently assigned password matches the one entered
or
is blank
o remove the password from all worksheets

The user could initially run the code multiple times for the 2 or 3
passwords currently in use.

The user has asked for one additional requirement. Since she uses dozens,
even hundreds of workbooks a day, she is asking if in lieu of logging into
each workbook with the same identical password, she could log in once into
a
"master" workbook.

As long as this workbook is open, it would provide the password needed to
open the second workbook, providing in essence a "password by session".
When
she leaves her desk, she will log out of the master workbook to prevent
others from loading any of the protected workbooks.

Thanks for your help.
Sprinks




"Zone" wrote:

Sprinks, are you saying you want to change the passwords of all the
worksheets in all the workbooks in a folder and its subfolder? How
complicated this will be depends on 1. Do all the worksheets in each
workbook have the same password? 2. Do all the worksheets in ALL the
workbooks have the same password? James
Sprinks wrote:
We have a number of worksheets containing sensitive data that are
protected
by password. Periodically, the firm's principal requests that we
change the
password for each. As there are a relatively large number of sheets
to
change, is there a means by which we could update the password for all
sheets
in a folder & its subfolders?

Thank you.
Sprinks

Sprinks, I agree with Nick. If you break this project down into
individual steps, you can focus on each step in turn. And if you get
stuck on a step, you can post a question on that step. James
NickHK wrote:
What you are after is certianly doable, but you need to break it down into
steps.

0. Ask the user for the new PW to use on all WBs.

1. Open/list files in a folder and subfolders:
See Bob's answer to "Recursive procedure" in this NG from 18/10.

2. Provide an InputBox to accept the user's input of the WB Password.

3. Using PW to attempt to open the first WBs from #1. Trap the error on
failure and optionally record the WB's name.

4. Check for protection on the WSs in the newly opened WB, asking the user
for PW input if necessary. Unprotect each.

5. Protect the WB with the PW from #0

6. Save/Close the WB

7. Loop to #3, until all WBs from #1 are processed.

As for the "session" password, maybe you need application level events, with
an InputBox routine in Personal.xls to request PW upon opening:
http://www.cpearson.com/excel/AppEvent.htm

NickHK

"Sprinks" wrote in message
...
Zone,

Thank you for your response. I'm glad you asked the question, because our
requirements are somewhat more complex than I originally thought. The
current situation is:

. Some workbooks have a password but their worksheets do not.
. Some workbooks have a password and passwords on their sheets as well.
. The passwords are currently not all the same due to poor management by
their previous owner. There is no need for them to be different, nor is
there a need to have a password for any worksheets. One on the workbook
itself is sufficient.
. Some workbooks in the current folder do not have a password.
. The VBA solution should:
o request the folder to operate upon
o request the current password from the user
o change the password of all workbooks in the folder and its
subfolders where the currently assigned password matches the one entered
or
is blank
o remove the password from all worksheets

The user could initially run the code multiple times for the 2 or 3
passwords currently in use.

The user has asked for one additional requirement. Since she uses dozens,
even hundreds of workbooks a day, she is asking if in lieu of logging into
each workbook with the same identical password, she could log in once into
a
"master" workbook.

As long as this workbook is open, it would provide the password needed to
open the second workbook, providing in essence a "password by session".
When
she leaves her desk, she will log out of the master workbook to prevent
others from loading any of the protected workbooks.

Thanks for your help.
Sprinks




"Zone" wrote:

Sprinks, are you saying you want to change the passwords of all the
worksheets in all the workbooks in a folder and its subfolder? How
complicated this will be depends on 1. Do all the worksheets in each
workbook have the same password? 2. Do all the worksheets in ALL the
workbooks have the same password? James
Sprinks wrote:
We have a number of worksheets containing sensitive data that are
protected
by password. Periodically, the firm's principal requests that we
change the
password for each. As there are a relatively large number of sheets
to
change, is there a means by which we could update the password for all
sheets
in a folder & its subfolders?

Thank you.
Sprinks

Nick and James,

Thank you for your help. I agree--Divide and Conquer.

Unfortunately, although I'm an experienced Access programmer, I don't have
much background in Excel. My attempt at the first step, getting the folder
name, displays the dialog box, but what is selected does not transfer to the
Folder Name: input box.

Am I missing something? I'm using Excel 2003 (11.8033.8036) SP2.

Thank you.
Sprinks

Sub ResetPasswords()
On Error Resume Next

Dim fd As FileDialog

' Request folder name
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.Show

End Sub


"Zone" wrote:

Sprinks, I agree with Nick. If you break this project down into
individual steps, you can focus on each step in turn. And if you get
stuck on a step, you can post a question on that step. James
NickHK wrote:
What you are after is certianly doable, but you need to break it down into
steps.

0. Ask the user for the new PW to use on all WBs.

1. Open/list files in a folder and subfolders:
See Bob's answer to "Recursive procedure" in this NG from 18/10.

2. Provide an InputBox to accept the user's input of the WB Password.

3. Using PW to attempt to open the first WBs from #1. Trap the error on
failure and optionally record the WB's name.

4. Check for protection on the WSs in the newly opened WB, asking the user
for PW input if necessary. Unprotect each.

5. Protect the WB with the PW from #0

6. Save/Close the WB

7. Loop to #3, until all WBs from #1 are processed.

As for the "session" password, maybe you need application level events, with
an InputBox routine in Personal.xls to request PW upon opening:
http://www.cpearson.com/excel/AppEvent.htm

NickHK

"Sprinks" wrote in message
...
Zone,

Thank you for your response. I'm glad you asked the question, because our
requirements are somewhat more complex than I originally thought. The
current situation is:

. Some workbooks have a password but their worksheets do not.
. Some workbooks have a password and passwords on their sheets as well.
. The passwords are currently not all the same due to poor management by
their previous owner. There is no need for them to be different, nor is
there a need to have a password for any worksheets. One on the workbook
itself is sufficient.
. Some workbooks in the current folder do not have a password.
. The VBA solution should:
o request the folder to operate upon
o request the current password from the user
o change the password of all workbooks in the folder and its
subfolders where the currently assigned password matches the one entered
or
is blank
o remove the password from all worksheets

The user could initially run the code multiple times for the 2 or 3
passwords currently in use.

The user has asked for one additional requirement. Since she uses dozens,
even hundreds of workbooks a day, she is asking if in lieu of logging into
each workbook with the same identical password, she could log in once into
a
"master" workbook.

As long as this workbook is open, it would provide the password needed to
open the second workbook, providing in essence a "password by session".
When
she leaves her desk, she will log out of the master workbook to prevent
others from loading any of the protected workbooks.

Thanks for your help.
Sprinks




"Zone" wrote:

Sprinks, are you saying you want to change the passwords of all the
worksheets in all the workbooks in a folder and its subfolder? How
complicated this will be depends on 1. Do all the worksheets in each
workbook have the same password? 2. Do all the worksheets in ALL the
workbooks have the same password? James
Sprinks wrote:
We have a number of worksheets containing sensitive data that are
protected
by password. Periodically, the firm's principal requests that we
change the
password for each. As there are a relatively large number of sheets
to
change, is there a means by which we could update the password for all
sheets
in a folder & its subfolders?

Thank you.
Sprinks