Excel XP & Win XP
I want to work with a group of workbooks, one at a time. I wanted to use
the following statement but I get a "Type Mismatch" error on the "For
Each..." line. All the workbooks are open.
My code, simplified, is:
Dim wbOther as Workbook
For Each wbOther In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
MsgBox wbOther.Name
Next wbOther
How can I setup a "For" loop for a group of workbooks?
Thanks for your time. Otto

dim i As long
dim arr

arr = Array("One", "Two", "Three")

for i = 0 to ubound(arr)
msgbox workbooks(arr(i)).Name
next i


RBS


"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I want to work with a group of workbooks, one at a time. I wanted to use
the following statement but I get a "Type Mismatch" error on the "For
Each..." line. All the workbooks are open.
My code, simplified, is:
Dim wbOther as Workbook
For Each wbOther In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
MsgBox wbOther.Name
Next wbOther
How can I setup a "For" loop for a group of workbooks?
Thanks for your time. Otto

Use a collection and not an array... Something like this...

Sub test()
Dim col As Collection
Dim wbk As Workbook

Set col = New Collection
col.Add Workbooks("Book2"), "Book2"
col.Add Workbooks("Book3"), "Book3"
col.Add Workbooks("Book4"), "Book4"

For Each wbk In col
MsgBox wbk.Name
Next wbk

End Sub
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

Excel XP & Win XP
I want to work with a group of workbooks, one at a time. I wanted to use
the following statement but I get a "Type Mismatch" error on the "For
Each..." line. All the workbooks are open.
My code, simplified, is:
Dim wbOther as Workbook
For Each wbOther In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
MsgBox wbOther.Name
Next wbOther
How can I setup a "For" loop for a group of workbooks?
Thanks for your time. Otto

Don't think that approach will work, but you could try

For Each wb OtherIn Workbooks

If Not IsError(Application.Match(wb.Name, (Array("One.xls",
"Two.xls", "Three.xks")), 0)) Then

MsgBox wbOther.Name
End If
Next wb


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I want to work with a group of workbooks, one at a time. I wanted to use
the following statement but I get a "Type Mismatch" error on the "For
Each..." line. All the workbooks are open.
My code, simplified, is:
Dim wbOther as Workbook
For Each wbOther In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
MsgBox wbOther.Name
Next wbOther
How can I setup a "For" loop for a group of workbooks?
Thanks for your time. Otto

Otto Moehrbach wrote:
Excel XP & Win XP
I want to work with a group of workbooks, one at a time. I wanted to use
the following statement but I get a "Type Mismatch" error on the "For
Each..." line. All the workbooks are open.
My code, simplified, is:
Dim wbOther as Workbook
For Each wbOther In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
MsgBox wbOther.Name
Next wbOther
How can I setup a "For" loop for a group of workbooks?
Thanks for your time. Otto


The following works:

Sub test12()
Dim x, y As Byte
x = Array("One.xls", "Two.xls", "Three.xls")
For y = LBound(x) To UBound(x)
Debug.Print Workbooks(x(y)).Name
Next y
End Sub

Alan Beban

Thanks guys. I see that I can't set up an array of workbooks so I went with
the array of workbook names. I just thought there was a better way.
Thanks again. Otto
"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I want to work with a group of workbooks, one at a time. I wanted to use
the following statement but I get a "Type Mismatch" error on the "For
Each..." line. All the workbooks are open.
My code, simplified, is:
Dim wbOther as Workbook
For Each wbOther In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
MsgBox wbOther.Name
Next wbOther
How can I setup a "For" loop for a group of workbooks?
Thanks for your time. Otto

I see that I can't set up an array of workbooks

You can set up an array of workbooks:

Sub test()

Dim i As Long
Dim arrWBs(1 To 2) As Workbook

Set arrWBs(1) = Workbooks("Book1")
Set arrWBs(2) = Workbooks("Book2")

For i = 1 To 2
MsgBox arrWBs(i).Name
Next

End Sub

Note that if the workbook hasn' t been saved yet you leave
the extension off, but if the workbook was saved then you add the .xla or
..xla.


RBS

"Otto Moehrbach" wrote in message
...
Thanks guys. I see that I can't set up an array of workbooks so I went
with the array of workbook names. I just thought there was a better way.
Thanks again. Otto
"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I want to work with a group of workbooks, one at a time. I wanted to use
the following statement but I get a "Type Mismatch" error on the "For
Each..." line. All the workbooks are open.
My code, simplified, is:
Dim wbOther as Workbook
For Each wbOther In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
MsgBox wbOther.Name
Next wbOther
How can I setup a "For" loop for a group of workbooks?
Thanks for your time. Otto

RB
Of course you are correct. I wasn't clear when I said that I can't
setup an array of workbooks. What I meant was that I couldn't setup an
array of workbooks in one line similar to what was in my original post:
For Each wbOther In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
Otto
"RB Smissaert" wrote in message
...
I see that I can't set up an array of workbooks

You can set up an array of workbooks:

Sub test()

Dim i As Long
Dim arrWBs(1 To 2) As Workbook

Set arrWBs(1) = Workbooks("Book1")
Set arrWBs(2) = Workbooks("Book2")

For i = 1 To 2
MsgBox arrWBs(i).Name
Next

End Sub

Note that if the workbook hasn' t been saved yet you leave
the extension off, but if the workbook was saved then you add the .xla or
.xla.


RBS

"Otto Moehrbach" wrote in message
...
Thanks guys. I see that I can't set up an array of workbooks so I went
with the array of workbook names. I just thought there was a better
way. Thanks again. Otto
"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I want to work with a group of workbooks, one at a time. I wanted to
use the following statement but I get a "Type Mismatch" error on the
"For Each..." line. All the workbooks are open.
My code, simplified, is:
Dim wbOther as Workbook
For Each wbOther In Workbooks(Array("One.xls", "Two.xls", "Three.xls"))
MsgBox wbOther.Name
Next wbOther
How can I setup a "For" loop for a group of workbooks?
Thanks for your time. Otto