Here's one for the uber-gurus.

I need to import multiple worksheets from the same workbook into 1 table.
(The data comprise more than 65,536 records, so they are split over several
worksheets.) I can do it this way, but it's a bit clumsy:

...
   nWorkSheets = GetNoWorkSheets(strPath)
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
                                              strTabUnavail, strPath, True,
""
   If nWorkSheets > 1 Then
       Call MsgBox("The workbook contains more than 1 sheet. " & vbCrLf _
                   & "You will need to import the remaining " & vbCrLf _
                   & "sheets manually, then close/open the view.", _
                   vbOKOnly, "ImportExcelReport()")
   End If
End Sub

Function GetNoWorkSheets(strPath) As Long
   Dim objXL As Excel.Application
   Dim ws As Excel.Worksheet
     
   Set objXL = New Excel.Application
   With objXL
       .Visible = False
       .Workbooks.Open (strPath)
       GetNoWorkSheets = .Sheets.Count
       .ActiveWorkbook.Close
   End With
End Function

I can't see a way to tell TransferSpreadsheet to use a particular worksheet;
it just grabs the 1st one. Am I missing something? Or, is there a hack, using
the API or something?