I have 6 workbooks. 5 of the workbooks are password protected to read and
modify because they contain very confidential payroll info. The 6th document
is a rollup doc that's linked to the other 5, but doesn't need to be password
protected, because the numbers are an aggregate - no sensitivity required.
When users go into the 6th document, they are asked to input 5 passwords -
which is a huge pain in the butt - is there a way to bypass these passwords
(without having to "cancel" out) and still have the information update?
thank you, Jess @ Lifetime Fitness

NoviceJESS...
I have 6 workbooks. 5 of the workbooks are password protected to read and
modify because they contain very confidential payroll info. The 6th
document
is a rollup doc that's linked to the other 5, but doesn't need to be
password
protected, because the numbers are an aggregate - no sensitivity required.
When users go into the 6th document, they are asked to input 5 passwords -
which is a huge pain in the butt - is there a way to bypass these
passwords
(without having to "cancel" out) and still have the information update?
thank you, Jess @ Lifetime Fitness

Hi Jess,

Yes, you can write a quick vba routine to open all five password protected
workbooks. But I don't think you want to open the confidential workbooks,
or do you?

If the 6th workbook contains links the prior five, does it not contain the
data from the prior five already?

I guess I am not clear on ~exactly~ what you are trying to do.

If you can be specific as possible (including workbook names) as to what you
are doing and what you want to accomplish, then someone will give you step
by step instructions and will likely write a small vba routine to do
whatever needs done. The vba (visual basic for applications programming
language should be very small, so it ought to be pretty easy.) Please
describe your process in more detail.

Regards,
Kevin

Hi Jess

Problem here is that the "update links ?" question happen on opening before
any macros run, so a macro can not avoid this or perform the necessary
actions in advance.

But if I understand your setup; you have 5 source files from maybe 5
differend people, containing sensitive information, and your file 6 contains
a controlled extract from those 5.

One solutions is to create a copy of your book 6. Name it book 7 and link
its formulas to book 6 instead of to the 5 source files. Now have your users
open book 7 instead, then there is only one link source and you may not even
have to protect it with a password. Just make sure 6 is opened and updated
once in a while, preferrably after each update in the 5 source files. A
macro in those can do that.

Other solutions would be to manually cancel the link update on opening and
later have a macro to update it. This is imo more vulnerable to user errors,
password changes, moved folders and stuff like that.

HTH. Best wishes Harald

"NoviceJESS" skrev i melding
...
I have 6 workbooks. 5 of the workbooks are password protected to read and
modify because they contain very confidential payroll info. The 6th
document
is a rollup doc that's linked to the other 5, but doesn't need to be
password
protected, because the numbers are an aggregate - no sensitivity required.
When users go into the 6th document, they are asked to input 5 passwords -
which is a huge pain in the butt - is there a way to bypass these
passwords
(without having to "cancel" out) and still have the information update?
thank you, Jess @ Lifetime Fitness

Hi Kevin - thanks so MUCH for attempting to answer my question (I've tried
everyone at work and we're all stumped). Here's more detail. In one folder
called "2005" - I have 6 workbooks. 5 of the workbooks belong to different
departments in the company. The 6th one is a rollup of all those numbers -
MTD and annualized, so the need for sensitivity is gone. Each fitness club
has a "2005" folder and we have 40 clubs. The Five documents have common
nomenclature across all clubs.
I'll pick one club to use as the example - Gilbert, AZ and their "2005"
folder contains the following:
First - the 5 source workbooks have 16 worksheets each. All worksheets are
password protected individually. The 5 workbooks also are "file protected" -
for open and write access.
The names a
1. "2005 Matrix GIL Aqua" (for Aquatics, password is ant)
2. "2005 Matrix GIL CC" (for Child Care, password is cow)
3. "2005 Matrix GIL Day Ops" (for Day operations, password is duck)
4. "2005 Matrix GIL Night Ops" (for Night Ops, password is nag)
5. "2005 Matrix GIL FD" (for Front Desk, password is frog)

The 6th doc is a rollup doc and has four worksheets - all worksheets are
protected individually (mainly for formulas and editing). The 6th doc for
Gilbert is called "GIL"

"GIL" does not need to be "file protected" - meaning - anyone can open, view
and enter in information. The worksheets in "GIL" are protected so that
people with little Excel skills don't try to add or remove rows or columns (I
hate that!).

Hope that helps - really really appreciate you taking the time to look into
this!
My email at work if it's easier is:
have a good one Kevin!
Jess

"Kevin H. Stecyk" wrote:

NoviceJESS...
I have 6 workbooks. 5 of the workbooks are password protected to read and
modify because they contain very confidential payroll info. The 6th
document
is a rollup doc that's linked to the other 5, but doesn't need to be
password
protected, because the numbers are an aggregate - no sensitivity required.
When users go into the 6th document, they are asked to input 5 passwords -
which is a huge pain in the butt - is there a way to bypass these
passwords
(without having to "cancel" out) and still have the information update?
thank you, Jess @ Lifetime Fitness

Hi Jess,

Yes, you can write a quick vba routine to open all five password protected
workbooks. But I don't think you want to open the confidential workbooks,
or do you?

If the 6th workbook contains links the prior five, does it not contain the
data from the prior five already?

I guess I am not clear on ~exactly~ what you are trying to do.

If you can be specific as possible (including workbook names) as to what you
are doing and what you want to accomplish, then someone will give you step
by step instructions and will likely write a small vba routine to do
whatever needs done. The vba (visual basic for applications programming
language should be very small, so it ought to be pretty easy.) Please
describe your process in more detail.

Regards,
Kevin

Hi Harald! thanks for the response - you know what's funny is I don't get an
"Update Links" dialog box - is that something I must have turned off?

Also - your solution to create an "intermediary" file would work if I didn't
have so many to manage and keep open and refresh, because here's the deal.
Each fitness club has these 6 documents - 5 are specific to depts within the
club and the 6th is a rollup document. These department managers also update
these 5 source docs all around the clock, because our clubs are open 24/7 -
the maintenance of opening & updating would get hairy.
But - back to the other thing - why do I not get an "Update Links" question?
you can email me at work if it's easier:
thanks again for the response & take care!
Jess

"Harald Staff" wrote:

Hi Jess

Problem here is that the "update links ?" question happen on opening before
any macros run, so a macro can not avoid this or perform the necessary
actions in advance.

But if I understand your setup; you have 5 source files from maybe 5
differend people, containing sensitive information, and your file 6 contains
a controlled extract from those 5.

One solutions is to create a copy of your book 6. Name it book 7 and link
its formulas to book 6 instead of to the 5 source files. Now have your users
open book 7 instead, then there is only one link source and you may not even
have to protect it with a password. Just make sure 6 is opened and updated
once in a while, preferrably after each update in the 5 source files. A
macro in those can do that.

Other solutions would be to manually cancel the link update on opening and
later have a macro to update it. This is imo more vulnerable to user errors,
password changes, moved folders and stuff like that.

HTH. Best wishes Harald

"NoviceJESS" skrev i melding
...
I have 6 workbooks. 5 of the workbooks are password protected to read and
modify because they contain very confidential payroll info. The 6th
document
is a rollup doc that's linked to the other 5, but doesn't need to be
password
protected, because the numbers are an aggregate - no sensitivity required.
When users go into the 6th document, they are asked to input 5 passwords -
which is a huge pain in the butt - is there a way to bypass these
passwords
(without having to "cancel" out) and still have the information update?
thank you, Jess @ Lifetime Fitness

Hi again Jess

There's a workbook setting for this in Tools Options. But where are you
prompted for the passwords then, if not in the "update links" process ?

Anyway, I think the real problem here is that you use Excel for what looks
like a database job. No matter how good we tune this, it will probably not
be the best solution for the task.

HTH. Best wishes Harald

"NoviceJESS" skrev i melding
...
Hi Harald! thanks for the response - you know what's funny is I don't get
an
"Update Links" dialog box - is that something I must have turned off?

Also - your solution to create an "intermediary" file would work if I
didn't
have so many to manage and keep open and refresh, because here's the deal.
Each fitness club has these 6 documents - 5 are specific to depts within
the
club and the 6th is a rollup document. These department managers also
update
these 5 source docs all around the clock, because our clubs are open
24/7 -
the maintenance of opening & updating would get hairy.
But - back to the other thing - why do I not get an "Update Links"
question?
you can email me at work if it's easier:
thanks again for the response & take care!
Jess

"Harald Staff" wrote:

Hi Jess

Problem here is that the "update links ?" question happen on opening
before
any macros run, so a macro can not avoid this or perform the necessary
actions in advance.

But if I understand your setup; you have 5 source files from maybe 5
differend people, containing sensitive information, and your file 6
contains
a controlled extract from those 5.

One solutions is to create a copy of your book 6. Name it book 7 and
link
its formulas to book 6 instead of to the 5 source files. Now have your
users
open book 7 instead, then there is only one link source and you may not
even
have to protect it with a password. Just make sure 6 is opened and
updated
once in a while, preferrably after each update in the 5 source files. A
macro in those can do that.

Other solutions would be to manually cancel the link update on opening
and
later have a macro to update it. This is imo more vulnerable to user
errors,
password changes, moved folders and stuff like that.

HTH. Best wishes Harald

"NoviceJESS" skrev i melding
...
I have 6 workbooks. 5 of the workbooks are password protected to read
and
modify because they contain very confidential payroll info. The 6th
document
is a rollup doc that's linked to the other 5, but doesn't need to be
password
protected, because the numbers are an aggregate - no sensitivity
required.
When users go into the 6th document, they are asked to input 5
passwords -
which is a huge pain in the butt - is there a way to bypass these
passwords
(without having to "cancel" out) and still have the information
update?
thank you, Jess @ Lifetime Fitness

NoviceJESS...
Hi Kevin - thanks so MUCH for attempting to answer my question (I've tried
everyone at work and we're all stumped). Here's more detail. In one folder
called "2005" - I have 6 workbooks. 5 of the workbooks belong to different
departments in the company. The 6th one is a rollup of all those numbers -
MTD and annualized, so the need for sensitivity is gone. Each fitness club
has a "2005" folder and we have 40 clubs. The Five documents have common
nomenclature across all clubs.
I'll pick one club to use as the example - Gilbert, AZ and their "2005"
folder contains the following:
First - the 5 source workbooks have 16 worksheets each. All worksheets are
password protected individually. The 5 workbooks also are "file
protected" -
for open and write access.
The names a
1. "2005 Matrix GIL Aqua" (for Aquatics, password is ant)
2. "2005 Matrix GIL CC" (for Child Care, password is cow)
3. "2005 Matrix GIL Day Ops" (for Day operations, password is duck)
4. "2005 Matrix GIL Night Ops" (for Night Ops, password is nag)
5. "2005 Matrix GIL FD" (for Front Desk, password is frog)

The 6th doc is a rollup doc and has four worksheets - all worksheets are
protected individually (mainly for formulas and editing). The 6th doc for
Gilbert is called "GIL"

"GIL" does not need to be "file protected" - meaning - anyone can open,
view
and enter in information. The worksheets in "GIL" are protected so that
people with little Excel skills don't try to add or remove rows or columns
(I
hate that!).

Hope that helps - really really appreciate you taking the time to look
into
this!
My email at work if it's easier is:
have a good one Kevin!
Jess


Hi Jess,

This on the surface doesn't look too difficult (famous last words).

I will send you an e-mail and we can go from there.

Regards,
Kevin