Importing multiple worksheets

[Guest]

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?

 

[Alex Dybenko]

Hi,
have a look here:
http://support.microsoft.com/kb/210379/en-us

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Thanks Ralph, Alex

Access's Help was a bit weak on this topic, but you have shown me that
TransferSpreadsheet works the way I had hoped it would.