excel linked documents password hold in code

[pennie.thomas]

hi there

i have a document which links to 14 other documents, which all have
passwords.

i would like to be able to update without having to remember all the
passwords.

does anyone have any idea on how to do this? a way of holding the
passwords in vba?

i think i will have to open and then close each doc as Excel will not
open all of them together.... runs out of memory...
any help! as always gratefully received....

x x x

 

[Dave Peterson]

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