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 - Reports_AccessToExcelAndWord.mdb
  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: Reports_AccessToExcelAndWord.mdb
    Posted: 07 Jul 2008 at 10:24pm
This sample db demonstrates export of Access reports to Excel and Word, with 
proper alignment and formatting, including lines and back colors. 

Reports sent directly from Access to Excel are known to suffer from the following 
shortcomings, making the output quite un-acceptable:
    (a) Substantial loss of alignment and formatting.
    (b) Labels and lines are lost.
    (c) Unwanted depiction of actual names of controls, cluttering up the overall 
    (d) Group footers do not get transferred properly. There is partial or total loss of 
    (e) Back colors (if any) of controls and sections are lost.

This sample db overcomes the above limitations by adopting the following 
    (a) Access report is first sent to Word. This facilitates retention of features other 
         than lines and back colors.
    (b) The report now in Word document, is copied to Excel.
    (c) In Excel, the report is dressed up and formatted, complete with lines and 
         back colors (Excel is well suited to perform such task).
    (d) Final version of formatted report in Excel is copied back to Word and saved 
         in word document format (Normally, when an access report is sent to word 
         via DoCmd.OutPutTo method, it always gets saved in rtf format, even if .doc 
         extension is used for destination file. For subsequent conversion to word 
         document format, explicit File > SaveAs action is needed).
    (e) With the steps outlined above, access report, duly formatted, complete with 
         lines and back colors becomes available in Excel as well as Word.
    (a) In the sample db, lines have been provided for column headers, group 
         footers and report footer. Back colors have been provided for group and 
         report footers.
    (b) Since individual page numbers do not serve any purpose in Excel, the reports 
         have been designed without page header and footer.
    (c) Two styles of reports are demonstrated:
        (i) R_Sales - This report has single row per record (CanGrow property of 
            various controls is set to No)
        (ii) R_Books - This report allows multiple rows per record (CanGrow property of 
            various controls is set to Yes). In order to preserve column-wise alignment 
            on transfer to Excel, programmatic padding up of individual columns by 
            place holders (dots) has been adopted so as to ensure that grown height of 
            each control in a record matches that of the tallest one. Subsequently, 
            during the dressing up process in Excel, these placeholder dots get 
    (d) Excel and Word versions of reports as created above, are stored in a 
         separate folder named REPORTS. This folder is housed in the parent folder 
         containing the sample db and gets created if not already existing.
5 - Versions - Access 2K
      (a) Excel 9.0 Object Library 
      (b) Word 9.0 Object Library
      (c) DAO 3.6
      (d) Microsoft Scripting RunTime

Download Access 2000: 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.