Read Excel file sheet names

Question

I have an export process that transfers data from my Access tables to an Excel File. A couple times I have had issues where the process didn't generate one or more of the sheets (1 sheet = 1 table) in Excel. So when the transfers are complete I want Access to check if all the sheets are located in the Excel file. I have most of the Check process worked out all I need now is a way to "read" the sheet names from the Excel File in to a table. How can I read the Sheet name (not the data)?

Answer 1

In Access 2007, You can use OpenDatabase method to do this:

Private Sub Command1_Click()
Set db = OpenDatabase("c:/123.xls", True, False, "Excel 5.0")
    For Each tbl In db.TableDefs
        MsgBox tbl.Name
    Next
End Sub

Answer 2

From Access you can automate Excel, open the workbook file, and read the sheet names from the Worksheets collection.

This sample uses late binding. If you prefer early binding, add a reference for Microsoft Excel [version] Object Library and enable the "early" lines instead of the "late" lines.

Give the procedure the full path to your workbook file as its pWorkBook parameter.

Public Sub List_worksheets(ByVal pWorkBook As String)
    'Dim objExc As Excel.Application ' early
    'Dim objWbk As Excel.Workbook ' early
    'Dim objWsh As Excel.Worksheet ' early
    Dim objExc As Object ' late
    Dim objWbk As Object ' late
    Dim objWsh As Object ' late

    'Set objExc = New Excel.Application ' early
    Set objExc = CreateObject("Excel.Application") ' late
    Set objWbk = objExc.Workbooks.Open(pWorkBook)
    For Each objWsh In objWbk.Worksheets
        Debug.Print objWsh.Name
    Next
    Set objWsh = Nothing
    objWbk.Close
    Set objWbk = Nothing
    objExc.Quit
    Set objExc = Nothing
End Sub