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



Barb Reinhardt wrote:
>
> 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

--

Dave Peterson

And include the index:

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

Dave Peterson wrote:
>
> 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
>
> Barb Reinhardt wrote:
> >
> > 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
>
> --
>
> Dave Peterson

--

Dave Peterson

Thanks, I'll try this.

"Dave Peterson" wrote:

> And include the index:
>
> For iCtr = LBound(myWkBks) To UBound(myWkBks)
> 'do more stuff
> msgbox mywkbks(ictr).name 'for example
> Next iCtr
>
> Dave Peterson wrote:
> >
> > 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
> >
> > Barb Reinhardt wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson
>

Dave,

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

Thanks,
Barb

"Dave Peterson" wrote:

> 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
>
>
>
> Barb Reinhardt wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>

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



Barb Reinhardt wrote:
>
> Dave,
>
> I won't know the workbook names until I do some other things. How do I
> dynamically create an array?
>
> Thanks,
> Barb
>
> "Dave Peterson" wrote:
>
> > 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
> >
> >
> >
> > Barb Reinhardt wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson