Your filename source is two columns C12:D24 - What is in each column?


"shorticake" wrote:

I have several workbooks that I need to open each month. The paths for these
workbooks are in cells C12:D24 (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