Maybe you could provide a workbook that opens those 17 workbooks first
(providing the passwords in code). Then open your real workbook.
I used 3 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 wkbks() As Workbook
Dim wkbkNames As Variant
Dim wkbkPwd As Variant
Dim iCtr As Long
Dim testStr As String
wkbkNames = Array("C:\book1.xls", _
"C:\book2.xls", _
"c:\my documents\excel\book3.xls")
wkbkPwd = Array("Pwd1", _
"Pwd2", _
"Pwd3")
If UBound(wkbkNames) = UBound(wkbkPwd) Then
'ok
Else
MsgBox "Design error--number of passwords <> number of workbooks!"
Exit Sub
End If
ReDim wkbks(LBound(wkbkNames) To UBound(wkbkNames))
For iCtr = LBound(wkbkNames) To UBound(wkbkNames)
Set wkbks(iCtr) = Nothing
On Error Resume Next
Set wkbks(iCtr) = Workbooks.Open(Filename:=wkbkNames(iCtr), _
Password:=wkbkPwd(iCtr))
On Error GoTo 0
If wkbks(iCtr) Is Nothing Then
MsgBox wkbkNames(iCtr) & vbLf & "was not opened!"
End If
Next iCtr
Application.Calculate
'close the first 17 (total - 1 workbooks)
For iCtr = LBound(wkbks) To UBound(wkbks) - 1
If wkbks(iCtr) Is Nothing Then
'do nothing
Else
wkbks(iCtr).Close savechanges:=False
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
==============
This area needs to be manipulated:
wkbkNames = Array("C:\book1.xls", _
"c:\my documents\excel\book2.xls")
Add 18 filenames to that list (17 linked + 1 real) and make sure that the last
workbook is the "real" workbook.