Opening Linked Workbooks from Summary

[zapszipszops]

Hi Everyone!
I have a summary workbook which links to 6 other workbooks which all
have different passwords to open them. I want to be able to open the
summary workbook and update the links without having to type in all the
passwords for each linked workbook. I was told 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 the code. I am still learning VBA
and any help is greatly greatly appreciated. Where do I start?

Thanks,
Julie

 

[Dave Peterson]

Saved from a previous post:

Untested.

Maybe you could have a helper workbook open your workbook and not update the
links.

Then it could open each of the 14 other workbooks (the links should refresh when
the workbook opens), then close that workbook and open the next.

Kind of like this:

Option Explicit
Sub testme()

Dim myFileNames As Variant
Dim myPasswords As Variant
Dim iCtr As Long
Dim myRealWkbk As Workbook
Dim myRealWkbkName As String
Dim wkbk As Workbook

myRealWkbkName = "C:\my documents\excel\book1.xls"

myFileNames = Array("C:\my documents\excel\book11.xls", _
"C:\my documents\excel\book21.xls", _
"C:\my other folder\book11.xls")

myPasswords = Array("pwd1", _
"pwd2", _
"pwd3")

If UBound(myFileNames) <> UBound(myPasswords) Then
MsgBox "check names & passwords--qty mismatch!"
Exit Sub
End If

Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0)

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

If wkbk Is Nothing Then
MsgBox "Check file: " & myFileNames(iCtr)
Exit Sub
End If

wkbk.Close savechanges:=False
Next iCtr

End Sub

(I got bored after 3 workbooks. You may want to test it with a couple to get it
going.)

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