Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheets and arrays
That seems perfect thank you - I'll try that now.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avg Arrays | Excel Worksheet Functions | |||
Write a formula using two arrays, across two worksheets | Excel Worksheet Functions | |||
Arrays | Excel Discussion (Misc queries) | |||
Arrays, & Worksheets & Grey Hair | Excel Discussion (Misc queries) | |||
Arrays | Excel Programming |