This sample db demonstrates import of scattered data from excel involving horizontally fragmented data blocks - into a single access table. In such a situation, assigning the data blocks to a common range name, followed by straightforward use of DoCmd.TransferSpreadsheet command does not work. Each data block has to be imported individually, for eventual amalgamation into a single table T_Imported. 

    Sample excel file (Orders.xls) is located in the home folder that contains this sample db. First row of each data block should carry field names.

    On opening the main form, and clicking the command button captioned "Load Excel File", the user gets the option either to load the sample excel file already available, or browse to and select any other excel file. The list box at right gets populated with the names of defined ranges, along with addresses of respective data blocks in selected excel file.

    Simultaneously, a text box located below the list box, gets populated with the cell addresses for respective data blocks. This text box 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 addresses).

    Note:
    Names collection in excel workbook covers the 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.

    User defined address string using comma as separator between adjacent blocks (no spaces) serves as one of the arguments for subroutine P_ImportFromExcelBrokenRanges. Either of the three sample styles given below can be used (Orders_01 is the name of worksheet):

    (a) Range Names:  "R1,R2, -- etc"
    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, avoid absolute addresses (Don't use $ sign anywhere)
    If style (c) is used, first row of spreadsheet (i.e. row 1) should carry field names. This style takes care of last populated row in the specified columns - automatically.

    Clicking of Import button carries out the import of different data blocks into temp tables followed by amalgamation into a single table T_Imported. The imported data gets displayed in the subform.

    Important:
    It is observed that DoCmd.TransferSpreadsheet action, if carried out as an integral part of automation code, can lead to sticking of 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

Version - Access 2000 File Format

Download File >>:  http://www.rogersaccesslibrary.com/forum/uploads/37/ExcelToAccessBrokenRanges.zip -