How do I export filtered Data from a form to Excel

[Jayz]

Ok, I'm almost there. I created a continuous form based on a query and then created some code that would export the results to Excel on a click of a button.
The problem is, when I filter the data on the form using the right click option "filter by selection", and then export it to Excel, the resultant data in excel is not filtered.

Can someone please provide a code that will export the filtered data from my form to Excel.

Thanks,
Jay

Here is the current code I'm using:
'********************************************
Private Sub Export_Click()
Dim xlApp As Object

'Check if Workbook is Open
If IsWorkbookOpen("OM_Search1.xls&quot = False Then

DoCmd.TransferSpreadsheet acExport, 8, "OM_Search_query1", "C:\Genie_Export\OM_Search1.xls", True

Set xlApp = CreateObject("excel.application&quot
xlApp.Visible = True
xlApp.Workbooks.open "c:\GENIE_Export\OM_Search1.xls" 'Path to your file

Else
'If IsWorkbookOpen("*.xls&quot = True And IsWorkbookOpen("OM_Search1.xls&quot = True Then

MsgBox "OM_Search1.xls is currently open. Please close the spreadsheet before exporting data. Note: current data will be overwritten", vbExclamation
End If


End Sub
'*******************************************************

 

[WildHare]

The Filter by Selection on the form is only a "temporary" filter, and, as you've seen, does not carry on to other actions, such as your TransferSpreadsheet method.

You'll need to implement the filtering at the query level itself with a criteria value.



Remember, you're unique - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[Jayz]

Just to curious as to why, when you filter on a form and then export it using the menu option "Analyze it with MS Excel" that it then works.

Surely there must be a code to replicate this action?

 

[B827]

In code on the form the Filter and Recordsource properties can be used to build SQL to emulate the filtering. Something like:

Dim SQLString
SQLString = "SELECT * FROM " & Me.RecordSource & " WHERE " & Me.Filter
Sandy