Help grabbing a list of Excel worksheets?

[PaulSpell]

Can anyone tell me how to grab a list of worksheets from an Excel workbook?

I am writing some import functions that use mutli sheet workbooks as a data source and need to create a list of the worksheets in a workbook, so that I can use this list to populate a list box and ask the user which sheet to import (same sort of thing Access does when you import a sheet from a workbook).

 

[Travis]

Look into using Excel Automation from VBA. You can Iterate through the WorkSheets and grab there names.

 

[PaulSpell]

That is what I mean, but how do you do it?

 

[Travis]

Code Provided is based off information from the following Microsoft Knowledge base article
142996

Public Sub ListExcelWorkSheets()
Dim i As Integer
Dim XL As Object
Dim WrkBook As Object
    Set XL = CreateObject("Excel.Application")
    Set WrkBook = XL.Workbooks.Open("C:\Temp\Book1.xls")
    For i = 1 To WrkBook.sheets.Count
       Debug.Print WrkBook.sheets(i).Name
    Next i%
    XL.Quit
    Set WrkBook = Nothing
    Set XL = Nothing


End Sub

 

[ghudson]

Nice job Travis. Is there any way to have the excel workbook close without asking if I want to save the changes? My workbook has AutoOpen code running and I would not want to save the workbook when it is closed via the code that is opening it to read the worksheet names.

Thanks!

 

[PaulSpell]

Thanks Travis thats exactly what I need. I spent ages looking through the knowledge base but couldn't find it.