Reading Data From Protected WorkBook

[rick]

Here is the situation... I have a Master spreadsheet with 20 tabs (one
for each employee) on the company network. Each customer has a
cooresponding spreadsheet that has just one tab to show their data
(which is a copy of their sheet from the master).

Since I dont want the users to go open the master, I had to password
protect the whole workbook. So When I open the employee workbook, it
prompts them for a password (as it should)

Is there a way to use vba to create a macro to unlock the Master
spreadsheet so it can read that data? The employees are not savy
enough to get into the vba code to get the password.

Any ideas?

 

[Guest]

Hi,

you could write a code to protect/unprotect a Master workbook in a separate
file ( Macro Recorder might help) and then send it to employees. But first
password protect the VBA module. Actually workbook password protection is
fairly easy to break using some iteration techniques, so note that Excel is
not very secure environment. VBA module protection works better.

 

[Guest]

Why open the Master?
When each employee opens his/her own workbook, that workbook's code can read
data from the master without opening the master. The user can then view,
add, delete, edit the data according to how you lock cells/ sheets in the
user's wbk.

You may or may not want users to update the master.

More information is needed.

 

[rick]

We will update the master with data every day... We then want the
users to open their version that just has the onesheet that reads from
the master. The problem is that if the master is password protected,
it prompts the user for the passwordwhen they open their spreadsheet.
We need the Master to be password protected so that the users dont open
it or update it.

I am thinking I can use vba in the client workbook to open the password
protected master so that the data can be read. The only problem I am
having is that I am trying to supply the password in the
Workbook_Open() function.... but the clients get promted forthe
master password before this function gets called.

Where should I put the code to supply the password for th Master so it
gets called before it tries to open it?

thanksguys!

 

[Guest]

The problem is that if the master is password protected,

it prompts the user for the passwordwhen they open their spreadsheet.

The key here is "when they open their spreadsheet"
Are they being asked for a password to their own spreadsheet?
Or
Are they being asked to supply a password for the Master Wbk?

If it is indeed the Master, then you probably have some code in each users
Workbook_Open event that is trying to open the Master as well as the
individual Slave Wbk? What I suggest is that you don't need to have the
Slave Wbk open the Master in order to get data FROM the Master? You can
leave the Master UNopened and just let each user open his/her Slave Wbk and
EXTRACT the data from the unopened Master.

There are several ways of doing this but for starters you might take a look
at this function on the Data menu: Data>Get External Data

From a Slave Wbk, try the function, with the Master closed.
Select Excel as the source, then find the Master Wbk from which you want to
extract the data. You will be asked where the data is to be placed in the
Slave Wbk.
Just indicate the upper left cell of a blank sheet or elsewhere as you wish.

When you have tried this manually and found the desired results, turn on the
macro recorder and do it again. The code will be generated for you. For max
efficiency you or someone familiar with VBA will need to streamline the code.

Then make a command button and assign it to this macro.