Reports_AccessToExcelAndWord.mdb |
Post Reply |
Author | |
A.D. Tejpal
Microsoft MVP Joined: 30 Jun 2008 Status: Offline Points: 192 |
Post Options
Thanks(0)
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 |
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
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 |