Workbooks Array

[Otto Moehrbach]

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 Smissaert]

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

 

[Jim Thomlinson]

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

 

[Bob Phillips]

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)

 

[Alan Beban]

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

 

[Otto Moehrbach]

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

 

[RB Smissaert]

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]

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