Sleeper: Password to update links

[Ken Puls]

Hey guys,

I haven't spent any time looking for this at all yet, I figued I'd just throw it out there in case anyone had already dealt with this...

My boss has aabout 40 budget spreadsheets which all link to each other. They're all password protected with the same password.

Problem is, when he opens one up, it prompts him for up to 10 passwords to update a the links. It really sucks if you mis-type one of the passwords as the links won't update.

Anyone know of an event to deal with this kind of issue, or a way around it? We can't remove the passwords, and we do want the links to update.

Any ideas?

[austenr]

Hi Ken,

Could you use something like this:


Sub UnProtectWorkbook()
Dim S As Object
Dim pWord5 As String, ShtName As String
pWord5 = InputBox("Please Enter the password")
If pWord5 = "" Then Exit Sub
ShtName = "Workbook as a whole"
On Error Goto errorTrap1
ActiveWorkbook.Unprotect Password:=pWord5
MsgBox "The workbook's structure has been Unprotected."
Exit Sub
errorTrap1:
MsgBox "Workbook could not be UnProtected - Password Incorrect"
Exit Sub

[Ken Puls]

Hmmm...

I guess one approach would be to strip the password temporarily from the remote workbooks and reapply it, but that seems kind of a weird way to go around it.

Here, to clarify my question a bit. I have the following workbooks:

wbMaster
wbDept100
wbDept200
wbDept300

Each has an identical password. wbMaster contains links to data in each of the Departmental workbooks (It's a consolidation effort).

So you open up wbMaster, and you get prompted 3 times to enter an identical password to update the links. Now if it was only 3, that would be a minor inconvenience. We have 17 departments with schedules and many other input workbooks.

What I want to do is enter the password once, have it open the workbook and update all links without having to enter the password again. I don't mind building a userform to do this, but I was hoping that there would be an easy way.

Even if I open a workbook with password supplied, I'll still get the prompt with the links, so how to get around it?

[austenr]

Ahhh..I see your delima. Opening the workbooks from within the wbMaster. Yes, that is a pain. Will do some research and try to get back to you.

[austenr]

Hi Ken...See if this will work for you..

http://www.ozgrid.com/forum/showthread.php?t=28111&highlight=linked+workbooks

[Ken Puls]

Thanks, Austen! I'll check that out!

:)