Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading Data From Protected WorkBook
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading Data From Protected WorkBook
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. -- ______ Regards, Greg " wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading Data From Protected WorkBook
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. " wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading Data From Protected WorkBook
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading Data From Protected WorkBook
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: DataGet 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. " wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading Data from another workbook... depending on variable in a cell? | Excel Discussion (Misc queries) | |||
Group, Outline, Protected Worksheets and Protected Workbook | Excel Programming | |||
Reading Writing Data from One WorkBook to Another | Excel Programming | |||
Open a password protected excel workbook from second workbook to fetch data using dynamic connection | Excel Programming | |||
reading password-protected, open webpage | Excel Programming |