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

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

ExportAccessToExcelMultiSheets.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: ExportAccessToExcelMultiSheets.mdb
    Posted: 09 Jul 2008 at 10:47pm
    This sample db demonstrates exporting of access data to excel workbook.    

    The excel workbook serving as data destination is displayed within a web 
browser control on the export form, while the access data meant to be exported is 
displayed alongside in a subform.

    Before clicking the export button, the cell meant to serve as top left column 
heading cell of exported  data block on desired worksheet should be selected (in 
the excel workbook displayed in browser control on the form).

    Multiple export of various queries to different worksheets can be conducted, one 
after the other. The destination worksheet in each case gets re-named as per 
access source query.

Note:
    (a) The destination excel file is copied from template excel file if available, and is 
          placed in folder named ExportedExcelFiles. This folder and the template 
          excel file are placed along the sample db in the same parent folder. 
          ExportedExcelFiles folder and or template excel file, if not already existing, 
          get created programmatically. The user can replace the sample template file 
          by any other desired excel file. However, it should be ensured that the name 
          of this file is always Template.xls
    (b) On the export form, command buttons and the combo box have been placed 
         in a subform 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 to use 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.

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/ExportAccessToExcelMultiSheets.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.