Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - ExcelToAccessBrokenRanges
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

>
Want a good read? Try The Summer of His Life (available on Amazon!)

ExcelToAccessBrokenRanges

 Post Reply Post Reply
Author
Message Reverse Sort Order
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: ExcelToAccessBrokenRanges
    Posted: 25 Aug 2011 at 11:37pm

ExcelToAccessBrokenRanges
Sample Db - Explanatory Notes

    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 >>:  uploads/37/ExcelToAccessBrokenRanges.zip

--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.