This sample db demonstrates importing of excel data (having first row as
column headings). Any desired data block from any desired worksheet can be
imported.
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 column heading 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: http://www.rogersaccesslibrary.com/forum/uploads/37/ExcelToAccessMultiSheets.zip - uploads/37/ExcelToAccessMultiSheets.zip
------------- --
Email: mailto:adtejpal@gmail.com - adtejpal@gmail.com
|