Open Multiple Workbooks

[Guest]

I have several workbooks that I need to open each month. The paths for these
workbooks are in cells C1224 (each path is actually a concatenation since
each file is updated monthly and then saved with a new name). When I run the
code that I've written below, it keeps erroring on the line for "MsgBox
"Check file: " & myFileNames(iCtr)."

I'd really appreciate any help and/or suggestions for improvements.


Sub OpenIBNRs()

Dim myFileNames As Variant
Dim myPasswords As Variant
Dim iCtr As Long
Dim wkbk As Workbook

Application.DisplayAlerts = False 'turn warnings off


myFileNames = Array(Range("c12:d24"))
myPasswords = "password"


For iCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Nothing
On Error Resume Next

ChDir "F:\EHP IBNR\"
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), UpdateLinks:=3, _
ReadOnly:=False, Password:=myPasswords(iCtr), _
WriteResPassword:=myPasswords(iCtr))

Sheets("Summary").Select

On Error GoTo 0

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

Next iCtr


End Sub

 

[Guest]

Your filename source is two columns C1224 - What is in each column?

 

[Guest]

Hi Jim,
The entire path with the file name are in these columns. It's probably
obvious, but I know very little about writing code, so typically alter
similar examples of what I'm trying to do. In this case, I'm over my head.
I'm trying to open several workbooks using the path that is in a range, in
this case C1224, all of which will have the same password.

Does this help?

 

[Guest]

what exactly is in C12 and what is in D12?

Hi Jim,
The entire path with the file name are in these columns. It's probably
obvious, but I know very little about writing code, so typically alter
similar examples of what I'm trying to do. In this case, I'm over my head.
I'm trying to open several workbooks using the path that is in a range, in
this case C1224, all of which will have the same password.

Does this help?

 

[Guest]

The formula in C12 is
=$B$5 & "\" & $B$6 & "\" & $B$7 & "\" & B12 & "\"&$C$11&"-"&A12&" "&$B$8&"
"&$B$10
and it returns
F:\EHP IBNR\FY 2008\03-September 07\Other EHP\AON\OP-AON IBNR Sep-07

The formula in D12 is
=$B$5 & "\" & $B$6 & "\" & $B$7 & "\" & B12 & "\"&$D$11&"-"&A12&" "&$B$8&"
"&$B$10
and it returns
F:\EHP IBNR\FY 2008\03-September 07\Other EHP\AON\IP-AON IBNR Sep-07

 

[Guest]

The two column array looks suspicious. I'm a bit in over my head here, but
maybe someone elase can chime-in... sorry -- Jim