Print Page | Close Window


Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Tejpal, A.D.
Forum Description: A.D. Tejpal's Library
Printed Date: 18 Mar 2018 at 4:31pm

Topic: ExportToExcelNamedMultiSheets
Posted By: A.D. Tejpal
Subject: ExportToExcelNamedMultiSheets
Date 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/


Email: -

Print Page | Close Window