Exporting filtered data to Excel

[Guest]

Hi Everyone,

I'm trying to export data to an excel spreadsheet from a database, and am
having some troubles getting it to do so with filtered data. I have a table
several thousand records long, with over 100 colums per record. The user
applies various Filter by Selection filters. What I need to do is to allow
the user to export to an excel spreadsheet, exporting only the filtered
records. As the:

DoCmd.OutputTo acOutputTable, stDocName, acFormatXLS, , True

command does not allow for filters, does anyone have any suggestions? I've
tried opening a report, etc, and exporting from there using an OnLoad event,
but that will only allow me to work with 20 Columns of data (and I need 'em
all!). Any advice would be greatly appreciated. Thanks!

 

[Guest]

It does not explicitly include filtering. It expects you will use a query to
apply whatever filtering you need.

You will have to write code translate the filters applied by the user into
SQL format. TransferSpreadsheet cannot use an SQL statement. It only
understands table and query names, so you will have to save your SQL to a
saved query the TransferSpreadsheet can use.

 

[google]

Hi,

What you can do is to query the data into Excel from Excel. In other
words, to let your users query your access database from Excel.
Building the right SQL statement allows them to bring back only those
rows that they like to see. This is how you can realise your filter.

If writing the SQL is difficult I can suggest using the SQL*XL utility
I wrote to do these kinds of jobs. SQL*XL can generate the select
statement for you with all the columns written out. Just remove what
you do not want to see. Then add a where clause to filter the data or
just bring back the whole lot into Excel.

Please have a look at SQL*XL at www.oraxcel.com/projects/sqlxl

Best regards, Gerrit-Jan Linker
Linker IT Software

 

[Guest]

What I have been thinking of doing is setting up a make table query, and
having the database create a new table composed of the filtered results. The
only problem I'm having now is passing Me.Filter as a criteria for the make
table query because it is dynamic, and I am not sure of how to do this.
Again, any help would be appreciated