Export Subform Filter to Excel

[meyerryang]

I have two tables that don't directly relate to eachother. I am using a
subform to filter in the records that I want to see. How can I export this
filtered list within the subform to an excel spreadsheet. When I use the
following code, I export the whole underlying table, and not the filtered
section.

DoCmd.OutputTo acOutputForm, "Lookup Subform", acFormatXLS, , True

Can someone point me in the right direction. Thanks in advance.

 

[Jeanette Cunningham]

Export a query that is the same as the (filtered) recordsource for the
subform.
DoCmd.OutputTo acOutputQuery, "YourQuery", acFormatXLS, , True


Jeanette Cunningham

 

[meyerryang]

I wish it was that easy. I could do it that way, but considering the tables
are being linked through oracle it would go from 2 seconds to about 8 minutes
of processing. I think I would need to create a recordset based on what is
on the form. If I can get the filtered subform to export to excel (based on
what is being viewed), then I could use that same subform for a multiple set
of forms (instead of creating a multiple set of queries).

Do you know how to create the recordset? Thanks.

 

[Jeanette Cunningham]

OutputTo in Help specifies all the different object types you can export, it
includes table and query.
Transfer Spreadsheet in Help specifies using a table or query, the query can
be a query string built in code, which I frequently use because it is very
easy to change the where clause to filter to just the data you want.
If you create one query to export, you can have the effect of multiple
queries just by changing the Where clause of the query.

I have never tried to export a recordset - so can't advise on this.

Jeanette Cunningham

 

[cc]

"meyerryang" <[email protected]> a écrit dans le message
de groupe de discussion :
(e-mail address removed)...

 

[Nicholas Scarpinato]

Your problem lies in the fact that a filter doesn't actually change the
viewed records as far as Access is concerned. All it does is hide what the
user sees. A query actually excludes records, which is what you're going to
need to do in order to make this work, because the export function in Access
exports everything in the table or query in question.

Why not just pull the data into a temp table in Access and run your queries
off of that temp table, rather than trying to filter the linked tables? It
might take a little longer, but it shouldn't take much longer than what
you're doing now, and it would be considerably less troublesome than trying
to query the linked tables directly. Plus you get the added benefit of only
having to access those tables once and then the time to process any
subsequent queries on them is reduced to almost nothing. Just update those
temp tables every few hours or so, depending on how often the tables they're
based on change. (Unless of course those linked tables are changing every few
seconds, in which case this might not work for you.)

 

[norbi]

meyerryang wrote on 01/24/2008 17:09 ET

I have two tables that don't directly relate to eachother. I am using
subform to filter in the records that I want to see. How can I export thi
filtered list within the subform to an excel spreadsheet. When I use th
following code, I export the whole underlying table, and not the filtere
section

DoCmd.OutputTo acOutputForm, "Lookup Subform", acFormatXLS, , Tru

Can someone point me in the right direction. Thanks in advance

Hi
the only way of exporting subform's data to Excel, I am aware of, is with us
o
MS-Access add-in 'A2EE.mda'
This add-in has been specially developed for exporting
- forms with subform
o
- datasheets with subdatasheet
from Access to Excel

With this add-in you can
- either send only those subform's data that are related to current main form'
recor
- or send all main form's and subform's dat
both in access-like drill-down structure

You may find examples and details on
http://www.limbersti.cz/A2EE
B
Norbert