Export Subform Filter to Excel

  • Thread starter Thread starter meyerryang
  • Start date Start date
M

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.
 
Export a query that is the same as the (filtered) recordsource for the
subform.
DoCmd.OutputTo acOutputQuery, "YourQuery", acFormatXLS, , True


Jeanette Cunningham
 
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.
 
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
 
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.)
 
Back
Top