'---------------------------------------------------------------------------------------
' Procedure : ListXlsSheets
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : List the sheet name of an Excel Workbook
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile - The Excel file to list the sheets
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2008-Jul-15                 Initial Release
'---------------------------------------------------------------------------------------
Function ListXlsSheets(sFile As String)
On Error GoTo Error_Handler
    Dim NumSheets   As Integer
    Dim i           As Integer
    Dim xlApp       As Object
    Dim xlWrkBk     As Object
    Dim xlWrkSht    As Object
    
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
    
    If Err.Number <> 0 Then
        'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set xlApp = CreateObject("excel.application")
    Else
        On Error GoTo Error_Handler
    End If

    xlApp.Visible = False 'make excel visible or not to the user
    Set xlWrkBk = xlApp.Workbooks.Open(sFile)
    
    NumSheets = xlWrkBk.Sheets.Count
    For i = 1 To NumSheets
        Debug.Print i & " - " & xlWrkBk.Sheets(i).Name
    Next i

    xlWrkBk.Close False
    xlApp.Close
    
    Set xlWrkSht = Nothing
    Set xlWrkBk = Nothing
    Set xlApp = Nothing
    
Exit Function

Error_Handler:
    If Err.Number <> 438 Then
        MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
        Err.Number & vbCrLf & "Error Source: ListXlsSheets" & vbCrLf & "Error Description: " & _
        Err.Description, vbCritical, "An Error has Occurred!"
        Exit Function
    Else
        Resume Next
    End If

End Function