This sample db demonstrates import of data from excel, spread over multiple sheets. Two styles are covered:

    (a) Excel workbook with pre-defined range names.
    (b) Excel workbook without pre-defined range names.

    Sample excel files (WithRangeNames.xls and WithoutRangeNames.xls) are located in the home folder that contains this sample db. First row of each data block carries field names.

    On opening the main form, and clicking the appropriate command button, the user gets the option either to load the sample excel file already available, or browse to and select any other excel file. In style (a), information pertaining to data blocks belonging to different named ranges as fetched from source file, is displayed in the subform at left. 

    In style (b) similar information is displayed, based upon programmatic detection of data blocks in excel workbook, matching the first field name provided by the user. For greater precision, if desired, the user can also optionally enter column count and name of last field.

    Amongst data blocks displayed in the subform, those required to be imported can be identified by clicking check boxes in column headed "Include". For items selected in this manner, the text box at right gets populated with a comma separated list of data blocks earmarked for import. Based upon user choice in the option group, this list is made up either of range names (applicable to style (a) only), or cell addresses or column addresses. 

    The list is editable, allowing the user to carry out any modification if desired. An option group provides a choice for the mode to be adopted for import action (i.e. whether as per pre-defined ranges or as per user specified cell or column addresses). 

    On clicking the appropriate command button, data blocks covered by above list get imported into access. For added convenience, command buttons have been provided for viewing the Imported data as well as the excel file - if desired. 

Supplementary Notes:
    1 - Names collection in excel workbook covers pre-defined ranges. Each element of this collection returns a Name object whose default property is Value. This default property returns range address. For example if the range object named R_DataBlock_1 covers cells C4:D7 on sheet DataBlock_1, Name object's value will return =DataBlock_1!$C$4:$D$7. For getting actual name of the range, we have to use Name property of Name object.

    2 - User defined address string using comma as separator between adjacent blocks (no spaces) serves as one of the arguments for import subroutine. Either of the three sample styles given below can be used (Orders_01 is the name of worksheet):
    (a) Range Names:  "R1,R2, -- etc"        - (Applicable to style (a) only)
    or
    (b) Address By Cells:  "Orders_01!B2:C12,Orders_01!E3:G13, -- etc"
    or
    (c) Address By Columns:  "Orders_01!B:C,Orders_01!E:G, -- etc"

    Note:  
    In (b) & (c) above, for smooth execution of DoCmd.TransferSpreadsheet method, avoid absolute addresses (Don't use $ sign anywhere)

    3 - Important:
    It is observed that DoCmd.TransferSpreadsheet action, if carried out as an integral part of automation code, can lead to hung excel application (excel refuses to quit despite explicit quit command and setting the object variables to nothing). For this reason, implementation of  DoCmd.TransferSpreadsheet is best carried out independently, after execution of automation code modules and closing of excel.

References:
    (a) DAO 3.6
    (b) Microsoft Scripting RunTime
    (For excel application, late binding has been used)

Version - Access 2000 File Format