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

Please consider donating $1 per sample downloaded, (find out why here)


 Post Reply Post Reply
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: ExcelToAccessMultiSheets.mdb
    Posted: 09 Jul 2008 at 10:51pm
    This sample db demonstrates importing of excel data (having first row as 
column headings). Any desired data block from any desired worksheet can be 

    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.

    (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.

    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)
        (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/

Back to Top
Sponsored Links

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.