Workbooks Array

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
O

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
 
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
 
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
 
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 said:
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
Click to expand...
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
 
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
 
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
 
Back
Top