Working with Workbook array

Excel XP, WinXP
I have a number of Excel files open, say One.xls, Two.xls, Three.xls. I
want to do something with each file in turn in a For loop. The "Test1"
macro has a "Type Mismatch" error in the "For" line. What is the correct
way to write this "For" line? I finally went with the Test2 macro but it
seems awkward. Thanks for your help. Otto
Sub Test1()
Dim wb As Workbook
For Each wb In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
MsgBox wb.Name
Next wb
End Sub

Sub Test2()
Dim wb As Workbook
Dim wbName As Variant
For Each wbName In Array("One.xls", "Two.xls", "Three.xls")
MsgBox Workbooks(wbName).Name
Next wbName
End Sub

The array is an array of workbook names not workbooks, so it is bound to
fail.

Awkward maybe, but method 2 seems as good as it gets to me.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Otto Moehrbach" <[email protected]> wrote in message
news:#IydgT#[email protected]...
> Excel XP, WinXP
> I have a number of Excel files open, say One.xls, Two.xls, Three.xls. I
> want to do something with each file in turn in a For loop. The "Test1"
> macro has a "Type Mismatch" error in the "For" line. What is the correct
> way to write this "For" line? I finally went with the Test2 macro but it
> seems awkward. Thanks for your help. Otto
> Sub Test1()
> Dim wb As Workbook
> For Each wb In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
> MsgBox wb.Name
> Next wb
> End Sub
>
> Sub Test2()
> Dim wb As Workbook
> Dim wbName As Variant
> For Each wbName In Array("One.xls", "Two.xls", "Three.xls")
> MsgBox Workbooks(wbName).Name
> Next wbName
> End Sub
>
>

Try:

Sub Test1()
Dim wb As Workbook
For Each wb In Workbooks
MsgBox wb.Name
Next wb
End Sub

Workbooks is a collection of the open workbooks.

try

Please Login or Register  to view this content.