update links to a password protected book!

[jjst34]

Hi,,
I have a workbook (asdf.xls), that has values from multiple password protected workbooks.. When the asdf.xls is opened it obviously asks if you want to udpate and if I hit yes, I have to enter every password.. Is there a way around this? I don't want to remove the passwords from the workbooks so others can't see info except for the person who's supposed to. But the workbook that has all the linked values in it is to be viewed by a manager, that doesn't want to open enter 30+ passwords every time it's opened, but does want it to update. Any help is appreciated. Thanks!

[mhabib]

Hello jjst34,

You could do try opening the linked files in your macro. In that case the passwords could be included in the OPEN commands. Since the linked files would already be open, the user will not be required to provide the passwords for updating the links.

M

[jjst34]

Originally posted by mhabib
Hello jjst34,

You could do try opening the linked files in your macro. In that case the passwords could be included in the OPEN commands. Since the linked files would already be open, the user will not be required to provide the passwords for updating the links.

M

there is no macro though.. it is just a cell formula that says =+G:\budgets\blah\blah.xls

but when the workbook is open it want's to update.. which I want.. I just don't want to be required to enter the pass..

maybe i am not following...

[royUK]

What mhabib is saying is that to achieve this you will need a macro.

[jjst34]

ahh ok.. any ideas on how to get started on this macro? I'm assuming it is to be in the workbook_open() but not sure what to put.. i did that
Application.AskToUpdateLinks = False

but now i need to tell it how to put in the 33 workbook passwords.. not a clue how to do that.. any help is appreciated. Thanks!

[jjst34]

any ideas where in the code i would put in all the worksheets passwords when it opens this workbook? Thx

[mhabib]

Dear jjst34,

Sorry for disappearing, but was too tied up. Thanks to royUK for clarifying my suggestion. You're right about the OPEN event - that's where the macro code needs to be. The setting for Not updating links also looks fine. Here's the code you asked for.

First, you'll need the names of all the files you are using - including full path names.

For each of these files you'll need to use the Workbooks.Open command

Eg, if you have a file
C:MypathFirstFile.xls,

With the password
abc

Then the command to open this file would be

Workbooks.Open FileName := "C:MypathFirstFile.xls", Password:= "abc"
...
And so on for each file.

This method will involve a lot of typing for 33 files. A better way would be to dump all your file names into Excel, and then use concatenation to build your Workbooks.Open commands. These can then be copy/pasted into the Visual Basic Editor to save on keyboarding.

Let me know if you need further help with this.

Regards,

M