Worksheets and arrays

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

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
 
Nick said:
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.
Click to expand...

Not necessarily

Any suggestions would be greatly appreciated
Click to expand...


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
 
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
 
Back
Top