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 - ExportToExcelNamedMultiSheets
  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: ExportToExcelNamedMultiSheets
    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/


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.