I have a Master workbook which links to 9 other workbooks. I want to update
the links without having to type in all the passwords for each linked
workbook. I assume the best way is to run a VBA script to open each linked
workbook with its associated password. Can someone please help me out with
this code or suggest an easier way.

Thanks,
J

I used two workbooks in my sample code. You can add as many workbook names and
passwords as you want--keep them in order, though:

Option Explicit
Sub auto_open()

Dim wkbk As Workbook
Dim wkbkNames As Variant
Dim wkbkPwd As Variant
Dim iCtr As Long
Dim testStr As String

wkbkNames = Array("C:\book1.xls", _
"c:\my documents\excel\book2.xls")

wkbkPwd = Array("Pwd1", _
"Pwd2")

If UBound(wkbkNames) = UBound(wkbkPwd) Then
'ok
Else
MsgBox "Design error--number of passwords < number of workbooks!"
Exit Sub
End If

For iCtr = LBound(wkbkNames) To UBound(wkbkNames)
Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=wkbkNames(iCtr), _
Password:=wkbkPwd(iCtr))
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox wkbkNames(iCtr) & vbLf & "was not opened!"
End If
Next iCtr

'ThisWorkbook.Close savechanges:=False
End Sub

When you're done testing (and after a save!), you can uncomment that .close
line. Then this workbook will open, then open the others, then close itself
(without saving!).

If all the workbooks were in the same folder, you could embed that folder name
in the .open line--instead of typing the path on each of the workbook names.

Kind of like:

Set wkbk = Workbooks.Open(Filename:="c:\my documents\" _
& wkbkNames(iCtr), _
Password:=wkbkPwd(iCtr))

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Jay Oken wrote:

I have a Master workbook which links to 9 other workbooks. I want to update
the links without having to type in all the passwords for each linked
workbook. I assume the best way is to run a VBA script to open each linked
workbook with its associated password. Can someone please help me out with
this code or suggest an easier way.

Thanks,
J

--

Dave Peterson