Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks Array
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks Array
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks Array
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks Array
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks Array
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks Array
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks Array
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create an array of all open workbooks | Excel Programming | |||
Opening Workbooks / Filling Array | Excel Programming | |||
SOS-How to pass array parameter to Workbooks.OpenText(...) in VC++ | Excel Programming | |||
Array Sheets Copy to new Workbooks | Excel Programming | |||
Use Array to activate workbooks | Excel Programming |