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

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