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: http://www.rogersaccesslibrary.com/forum/uploads/37/ExportAccessToExcelMultiSheets.zip - uploads/37/ExportAccessToExcelMultiSheets.zip
------------- --
Email: mailto:adtejpal@gmail.com - adtejpal@gmail.com
|