[AccessD] Access 2007 - Exporting Report (with Dynamic Filters) toExcel

Brad,

    My sample db named Reports_AccessToExcelAndWord might also be of interest to you. It is in access 2000 file format and is available at Rogers Access Library. 

Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Brad Marks 
  To: accessd at databaseadvisors.com 
  Sent: Friday, July 16, 2010 03:45
  Subject: [AccessD] Access 2007 - Exporting Report (with Dynamic Filters) toExcel


  We have an Access 2007 report that has "dynamic filters" which enable the
  end-users to trim down the amount of data on the report.  This is done with
  code like this...

  Reports.Report001.Filter = var_Where

  Reports.Report001.FilterOn = True


  This works nicely.

  Now there is a need to export this report and other similar reports to
  Excel.

  We have experimented with "DoCmd.OutputTo acOutputReport" command but the
  results are not consistent.  Sometimes only the headings are exported, in
  other cases more of the report is exported.

  >From what we have read, the "DoCmd.TransferSpreadsheet" command is a better
  method to use.  The catch is that this command can export from either a
  table or query, but not from a report.  We have experimented with this
  approach and it works well, except we have a problem when a user is using
  the dynamic filter on the report.

  For example, we might have 500 records returned by the underlying query.  If
  we use the "DoCmd.TransferSpreadsheet" command we will see 500 rows in the
  generated Excel spreadsheet.  This is correct if no dynamic report filters
  are used.  Let's say that a user employs one of the dynamic report filters
  and the resulting report now has 100 records shown on the report.  We would
  then like to only have 100 records on the generated Excel table if the user
  chooses to push the "Export to Excel" button that we have provided on the
  report.

  We must be missing something.  Is there an easy solution?

  Thanks in advance for your help and insights.

  Brad