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