Worksheets and arrays
 

Given a variable that is a filename does anyone know how
to retrieve a list of all worksheets within that file and
place them in an array?

I eventually want the list to appear in a combo box so I
am assuming an array is the easiest way of doing this.

Any suggestions would be greatly appreciated

Nick

Worksheets and arrays
 

"Nick" wrote in message
...
Given a variable that is a filename does anyone know how
to retrieve a list of all worksheets within that file and
place them in an array?

I eventually want the list to appear in a combo box so I
am assuming an array is the easiest way of doing this.


Not necessarily


Any suggestions would be greatly appreciated



This code should do the job nicely

Dim xlbook as Workbook , xlsheet as Worksheet

Set xlbook = xlapp.Workbooks.Open(wkbname)
For Each xlsheet in xlbook.Worksheets
myComboBox.Additem xlsheet.name
Next xlsheet

xlbook.Close

Keith

Worksheets and arrays
 

That seems perfect thank you - I'll try that now.

Worksheets and arrays
 

nice.
As a tip, its not always necessry to DIM an object to use
it. In this context, it probably is, since I'd imagine
using th ecombo to anable the user to manipulate whe
workbook.
However, if say you're working with the scripting
filesystemobject, then the following is quite useful.

Sub tester()
Dim wkbookname As String
Dim xlsheet As Worksheet

wkbookname = Application.GetOpenFilename()
With Workbooks.Open(wkbookname)
For Each xlsheet In .Worksheets
Debug.Print xlsheet.Name
Next xlsheet
.Close False
End With

End Sub

Note that th eworkbook object is implied in memory with
the WITH statement, and also that clean-up will occur
with the End With statement.

Hope that this may be of use in future

regards
PAtrick Molloy
Microsoft Excel MVP

-----Original Message-----

"Nick" wrote in
message
...
Given a variable that is a filename does anyone know
how
to retrieve a list of all worksheets within that file
and
place them in an array?

I eventually want the list to appear in a combo box so
I
am assuming an array is the easiest way of doing this.


Not necessarily


Any suggestions would be greatly appreciated



This code should do the job nicely

Dim xlbook as Workbook , xlsheet as Worksheet

Set xlbook = xlapp.Workbooks.Open(wkbname)
For Each xlsheet in xlbook.Worksheets
myComboBox.Additem xlsheet.name
Next xlsheet

xlbook.Close

Keith


.