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 - ExcelToAccessTransposed.mdb
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

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

ExcelToAccessTransposed.mdb

 Post Reply Post Reply
Author
Message
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: ExcelToAccessTransposed.mdb
    Posted: 09 Jul 2008 at 10:50pm
    This sample db demonstrates importing of excel data, where contents of 
individual fields are arranged down the rows (as against the conventional layout of 
fields across the columns). Any desired data block from any desired worksheet can 
be imported. 

    After importing into access, the fields are transposed into normal layout via a 
cross tab query. This is done in accordance with the list of desired field names as 
contained in table T_Hdg.

    A common dual-purpose query is used for appending/updating data in local 
table T_Local as per data imported into table T_Imported. Fresh records get 
appended to T_Local, while those for which matching primary key value already 
exists, get updated as per latest imported data.

    As added convenience, the excel workbook serving as data source is displayed 
within a web browser control on import form, while the results (appended/updated 
records) are displayed alongside in a subform. The imported data is highlighted in 
separate colors depending upon whether the records are  appended or updated.

    Before clicking the import button, top left cell of desired data block on desired 
worksheet should be selected in the excel workbook displayed in browser control 
on the form. The area pertaining to this data block is detected automatically, gets 
highlighted in color and is assigned a dynamic range name. Import process 
continues further only when user is satisfied that correct data block has been 
highlighted.

    Multiple import of various data blocks in different worksheets can be conducted, 
one after the other.

    Note:
    (a) The source excel file can be of any name, but should be in the same folder 
         as the one containing the sample db. It is detected automatically and all 
         further action takes place on a temp copy, so as to avoid any interference to 
         the original.
    (b) On the import form, labels have been used instead of command buttons in 
         order to prevent freezing of web browser, which otherwise takes place when 
          some excel cell is clicked by user prior to clicking of command button.

    (c) It is observed that with use of browser control for opening the excel 
         workbook, Excel process may not terminate at the end of session, even 
         though Quit command has been used and all object variables set to nothing. 
         After the access db is finally closed, it would be desirable to cross check via 
         task manager and terminate the Excel process if still running.

Caution:
    Before trying this sample db, please ensure that activex control named 
    "Microsoft Web Browser" is registered in your access installation. For this, file 
    named shdocvw.dll should be available in System32 folder of windows operating 
    system. Click Tools > Activex Controls to get the dialog box for Activex controls 
    and check whether this control appears in the list. If not, click Register button, 
     locate the file in System32 folder and do the needful.
 
5 - Versions - Access 2K/XP/2K3 (Access 2000 File Format)
      References: 
        (a) Microsoft Excel Object library (version 9.0 or later)
        (b) Microsoft Scripting RunTime
        (c) DAO 3.6
Download Access 2K/XP/2K3: uploads/37/ExcelToAccessTransposed.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.