Open Multiple Workbooks
 

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

Open Multiple Workbooks
 

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

Open Multiple Workbooks
 

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 C12:D24, all of which will have the same password.

Does this help?

"Jim May" wrote:

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

Open Multiple Workbooks
 

what exactly is in C12 and what is in D12?


"shorticake" wrote:

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 C12:D24, all of which will have the same password.

Does this help?

"Jim May" wrote:

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

Open Multiple Workbooks
 

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


"Jim May" wrote:

what exactly is in C12 and what is in D12?


"shorticake" wrote:

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 C12:D24, all of which will have the same password.

Does this help?

"Jim May" wrote:

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

Open Multiple Workbooks
 

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

"shorticake" wrote:

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


"Jim May" wrote:

what exactly is in C12 and what is in D12?


"shorticake" wrote:

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 C12:D24, all of which will have the same password.

Does this help?

"Jim May" wrote:

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