Export Subform Filter to Excel

116 views
Skip to first unread message

meyerryang

unread,
Jan 24, 2008, 5:09:54 PM1/24/08
to
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

unread,
Jan 24, 2008, 5:28:49 PM1/24/08
to
Export a query that is the same as the (filtered) recordsource for the
subform.
DoCmd.OutputTo acOutputQuery, "YourQuery", acFormatXLS, , True


Jeanette Cunningham


"meyerryang" <meyer...@discussions.microsoft.com> wrote in message
news:7A3C19DB-DA4D-406D...@microsoft.com...

meyerryang

unread,
Jan 25, 2008, 9:07:01 AM1/25/08
to
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

unread,
Jan 25, 2008, 3:01:19 PM1/25/08
to
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

"meyerryang" <meyer...@discussions.microsoft.com> wrote in message

news:F35B1A72-A5A6-41B8...@microsoft.com...

cc

unread,
Feb 8, 2008, 1:01:47 PM2/8/08
to

"meyerryang" <meyer...@discussions.microsoft.com> a écrit dans le message
de groupe de discussion :
7A3C19DB-DA4D-406D...@microsoft.com...

Nicholas Scarpinato

unread,
Feb 13, 2008, 1:34:01 PM2/13/08
to
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.)

noreply@noreply

unread,
Sep 27, 2008, 11:19:57 AM9/27/08
to
E for Everyone eradicated a lot of intangible the added period, when it had people vagabondage around extracurricular PAX wow gold[/url:2jmn6y86] with televisions shoved up their shirts, handing out E4E advertising ordure. Their supervisor organizer's smarmy cognition, an 'Oh, we didn't harmonise that this else minuscule con was achievement on' belike broke (http&#58;//www&#46;ugamegold&#46;com/:2jmn6y86)t of what was paw.

norbi

unread,
Jun 16, 2012, 3:50:11 PM6/16/12
to
Hi,
the only way of exporting subform's data to Excel, I am aware of, is with use
of
MS-Access add-in 'A2EE.mda'.
This add-in has been specially developed for exporting:
- forms with subforms
or
- datasheets with subdatasheets
from Access to Excel.

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

You may find examples and details on:
http://www.limbersti.cz/A2EE/
BR
Norbert
Reply all
Reply to author
Forward
0 new messages