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

>
Want a good read? Try The Summer of His Life (available on Amazon!)

Reports_AccessToExcelAndWord.mdb

 Post Reply Post Reply
Author
Message
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 
         display.
    (d) Group footers do not get transferred properly. There is partial or total loss of 
         content.
    (e) Back colors (if any) of controls and sections are lost.

This sample db overcomes the above limitations by adopting the following 
approach:
    (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.
 
    Note: 
    (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 
            eliminated.
    (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
      References:
      (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/Reports_AccessToExcelAndWord.zip

--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

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.