ExcelToAccessTransposed.mdb |
Post Reply |
Author | |
A.D. Tejpal
Microsoft MVP Joined: 30 Jun 2008 Status: Offline Points: 192 |
Post Options
Thanks(0)
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 |
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |