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

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________




Brad,

in my experience transfer spreadsheet can prove troublesome.  I prefer to use ADO recordsets.  I have made up a couple of examples here. These are Excel based and driven, but it is easy to move the code to Access and drive the whole show from there instead.

<<http://www.excelyourbusiness.com.au/FormulaHelp.htm#FormulaExcelToAccessTnf>>

maybe that will help a bit.

Cheers
Darryl. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Friday, 16 July 2010 8:16 AM
To: accessd at databaseadvisors.com
Subject: [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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended 
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review, 
retransmission, dissemination or other use of, or taking of any action in reliance 
upon this information, by persons or entities other than the intended recipient is 
prohibited.

If you have received this in error, please contact the sender and delete this e-mail 
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute 
the information contained in this e-mail and any attached files, with the permission 
of the sender.

This message has been scanned for viruses.
_______________________________________________________________________________________