|Please consider donating $1 per sample downloaded, (find out why here)|
Joined: 30 Jun 2008
Posted: 17 Sep 2008 at 5:43am
Author: A.D. Tejpal
This sample db demonstrates a drastically simplified method for exporting multiple access tables/queries to custom named worksheets in the given excel workbook. No automation code is needed.
If the user is not very particular about pre-defining ancillary content in the destination file and positioning of starting cell for exported data, DoCmd.TransferSpreadsheet method can be used directly. (DoCmd.OutputTo method is not convenient as it is object specific and overwrites preceding export - if any).
Names of tables / queries meant to be exported are placed in a table named T_ExportList, having fields QueryName (name of table or query required to be exported) and DestnSheetName (desired name of worksheet to which you wish to export the table or query). User can select the items to be exported by clicking the Yes/No type field named DoExport.
Sample subroutine P_ExportToExcelMultiSheet() is used to export all selected tables / queries to different sheets in destination excel file. Respective sheets in excel file get named as per those prescribed in field DestnSheetName.
This procedure uses sample subroutine named P_ExportToExcelNamedSheet. It accepts three arguments, i.e. SourceQueryName, ExcelSheetName and ExcelFilePath.
The subroutine creates a temporary copy of table/query as per the name of destination worksheet, does the export and then deletes the temporary copy.
Note - If DestnSheetName is left blank, the target worksheet gets named as per the table / query that has been exported to it.
Version: Access 2000 File Format
(a) Microsoft Excel Object library (version 9.0 or later)
(b) Microsoft Scripting RunTime
(c) DAO 3.6
Download >> uploads/37/ExportToExcelNamedMultiSheets.zip
|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