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: http://www.rogersaccesslibrary.com/forum/uploads/37/Reports_AccessToExcelAndWord.zip -
|