Woo hoo! It works
For the record the macro ended up looking like
Option Explicit
Sub WorkBook_Open()
Call OpenSummary '<--- Name of pre-written macro
End Sub
Sub OpenSummary()
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 = "F:\Security\Logging\Security Averages.xlsx"
myFileNames = Array("F:\Security\Logging\Aaron.xlsx", _
"F:\Security\Logging\Aidan.xlsx", _
"F:\Security\Logging\Alfredo.xlsx", _
"F:\Security\Logging\Brad.xlsx", _
"F:\Security\Logging\Jacqui.xlsx", _
"F:\Security\Logging\Jenny.xlsx", _
"F:\Security\Logging\John.xlsx", _
"F:\Security\Logging\Kian.xlsx", _
"F:\Security\Logging\Louise.xlsx", _
"F:\Security\Logging\Michelle.xlsx", _
"F:\Security\Logging\Mirawati.xlsx", _
"F:\Security\Logging\Steve.xlsx")
myPasswords = Array("aar66", _
"aid11", _
"alf99", _
"brad33", _
"jacq77", _
"jen22", _
"john00", _
"kia55", _
"lou88", _
"mich77", _
"mira11", _
"sev44")
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
' MsgBox iCtr & vbLf & myFileNames(iCtr) & vbLf & myPasswords(iCtr)
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _
Password:=myPasswords(iCtr), ReadOnly:=True)
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
Thanks for all the help Dave - I couldnt have done this without your knowledge
