Array of workbooks. Can this be done?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This question really relates to something I want to do in PowerPoint, but I'm
guessing that the same type of thing would apply in Excel as well.

I'd like to identify four workbooks to open programmatically. I'd like to
assign them to oWB1, oWB2, oWB3 and oWB4 as they are opened. Can I refer to
them like this

for i = 1 to 4
'do things with workbook owb & i
next i

Really what I'm doing is opening presentations and identifying them in a
similar way. The presentations are physically located on a server that takes
a while to access, so I don't want to open and close them repeatedly.

Thanks
 
I think I'd use an actual array:

Option Explicit
Sub testme01()

Dim myWBNames As Variant
Dim myWkBks() As Workbook
Dim iCtr As Long

myWBNames = Array("C:\a.xls", "c:\b.xls", "C:\c.xls")

ReDim myWkBks(LBound(myWBNames) To UBound(myWBNames))

For iCtr = LBound(myWkBks) To UBound(myWkBks)
Set myWkBks(iCtr) = Workbooks.Open(Filename:=myWBNames(iCtr))
'do more stuff right after opening
Next iCtr

'or wait until they're all open and do stuff then
For iCtr = LBound(myWkBks) To UBound(myWkBks)
'do more stuff
Next iCtr

End Sub
 
And include the index:

For iCtr = LBound(myWkBks) To UBound(myWkBks)
'do more stuff
msgbox mywkbks(ictr).name 'for example
Next iCtr
 
Thanks, I'll try this.

Dave Peterson said:
And include the index:

For iCtr = LBound(myWkBks) To UBound(myWkBks)
'do more stuff
msgbox mywkbks(ictr).name 'for example
Next iCtr
Click to expand...
 
Dave,

I won't know the workbook names until I do some other things. How do I
dynamically create an array?

Thanks,
Barb
 
dim myWBNames() as string
dim wCtr as long

wctr = 0
if something is true then
wctr = wctr +1
redim preserve mywbnames(1 to wctr)
mywbnames(wctr) = "c:\whateveryouneedhere"
end if

if wctr = 0 then
'no filenames found
else
'do the work
end if
 
Back
Top