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
.
|