[AccessD] Report to Excel_

Mark,

    Reports sent directly from Access to Excel are known to suffer from the following shortcomings, making the outcome rather 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.

    Chronic limitations outlined above, can be overcome  by routing the report via Word and performing final dressing up and formatting within Excel application, which happens to be well suited to perform such a task. With this, the Excel report attains a look basically as good as that of a normal Access report.

    My sample db named Reports_AccessToExcelAndWord, demonstrates export of Access reports to Excel, with proper alignment and  formatting, including all group headers/footers positioned appropriately, along with lines and back colors. Final version of Excel report also gets saved in word document format.

    This sample db (in Access 2000 file format) is available at Rogers Access Library (other developers library). Link - http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.

    References - Excel 9.0 Object Library, Word 9.0 Object Library, DAO 3.6, Microsoft Scripting RunTime.

    If you wish to pursue further as per the approach outlined above, you might like to first try out the sample db as it is (without making any modification) and confirm whether it works smoothly at your end.

Best wishes,
A.D.Tejpal

  ----- Original Message ----- 
  From: Mark A Matte 
  To: accessd at databaseadvisors.com 
  Sent: Tuesday, May 08, 2007 22:25
  Subject: Re: [AccessD] Report to Excel_


  Thanks Jim,

  I need to export a report because of the conditional formatting.  
  TransferSpreadsheet won't work with reports.

  Any thoughts?

  Thanks,

  Mark A. Matte