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" wrote in message
...
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

Code:
--------------------

Sub test()
Dim wb As Workbook, wbNames, x
wbNames = Array("One.xls","Two.xls",Three.xls")
For Each wb In WorkBooks
x = Application.Match(wb.Name, wbNames, 0)
If Not IsError(x) Then MsgBox wb.Name
Next
End Sub
--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=551995