Export filtered data

[kafedral]

hello, i've looked around the site and found a way to export my queries to excel with this code:

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tblDepartments", "C:\Test1.xls", False

but i still want to filter my queries before i export them, ether that or i need to make a button that exports the filtered data in forms and reports just like the one in the "external data" menu.

Thank you for your help.

 

[kafedral]

can anyone point me to a site that could help me solve this problem?

 

[Phildaburn]

Is the filter for tblDepartments the same every time? If so, create it as a query and export the query in the same manner as you've coded to export the table.
Phil...

 

[kafedral]

@Phil
ye.. but that is not the case, in my case i have eight different filters for like twenty queries, and i cant leave the top panel visible.

so can anyone help me with this?

 

[alansidman]

Have you considered building a search form with unbound text boxes that contain your filter criteria? Use the text boxes as the criteria in your query to filter it. Then your results are filtered and can be exported with out any additional work. Here is a link on how to build a search form that will allow you or your users to filter your query.

http://www.datapigtechnologies.com/flashfiles/searchform.html

Alan

 

[Phildaburn]

I'm not sure this will help, but perhaps you should explore the Help files on the Filter() function.

Returns a zero-based array containing subset of a string array based on a specified filter criteria.
Syntax
Filter(sourcearray, match [, include ] [, compare ] )

Phil...

 

[kafedral]

@ Alan
I already have all my forms containing the data that i need but my problem is how to make a button that exports filtered data to excel and so far i found a way to do so using only my queries and tables.

@ Phil
Thanks a lot for your help that sounds like a great idea, I've looked up this function but I'm not so good with VBA so this is what i came up with

Private Sub Command17_Click()
    Dim xTarget As Variant
    xTarget = Filter("qryOrders", "Buy the Book")
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, xTarget, "C:\Test1.xls", False
    MsgBox ("Export Complete")
End Sub

and as expected it's not working... if anyone can help me with that it will be great.

 

[jackd]

I'm confused with your statement

@ Alan
I already have all my forms containing the data that i need but my problem is how to make a button that exports filtered data to excel and so far i found a way to do so using only my queries and tables.

A form is a way to present data from an underlying Table or Query.
Whatever the Record source is for the Form, will be the "thing" (table or query) you would export.

Perhaps you could clarify exactly what the issue is.

 

[boblarson]

If you want to send a filtered form's data to Excel - Copy my function from here

http://www.btabdevelopment.com/ts/default.aspx?PageId=47

and paste it into a standard module. Then you can simply pass the form name to it and it will export the current filtered version and not the entire recordset.

 

[kafedral]

@ boblarson
Thanks a lot, that is exactly what i needed! and it works just fine