General: Importing Excel spreadsheets from code

Use the TransferSpreadsheet method. For example, to import the entire 'tab' or worksheet, use the following syntax

    DoCmd.TransferSpreadsheet transfertype:=acImport, _
            tablename:="tmpTableName", _
            FileName:="SomeExcelFile", Hasfieldnames:=True, _
            Range:="'WorkSheet Name'!", SpreadsheetType:=5
            'The Spreadsheet type = 5 specifies an Excel 5.0/7.0 file
            'format

Note the quote within the range. If your worksheet name as a space, you have
to use a quote, otherwise you can just pass the name.
Range:="WorksheetName!"

To import a specific range within a worksheet, use the following syntax

    DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
            tablename:="tmpTableName", FileName:="SomeExcelFile", _
            Hasfieldnames:=False, Range:="WorkSheet!B1:B11"
            'This will import the range B1 through B11
            'The Spreadsheet type = 5 specifies an Excel 5.0/7.0 file
            'format

Note:

The range can also be a number of columns e.g.

Range:="WorkSheet!B:D"

which will import the data in columns B, C, D.