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

Brad:

I would use an append query to append the filtered records to a temp export
table from inside the report (maybe Open event?) that would incorporate the
dynamic report filters.  You could build it in SQL, add the 'WHERE' clauses
and use db.Execute to run it.  

Rocky



  ----- 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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com