So, I'm using my favorite search code from here:
http://allenbrowne.com/ser-62code.html

Once I have everything filtered on the form, the users want the
ability to export that data to Excel. Unfortunately that exports
everything.

How can I export the filtered results? I'm at a loss.

Thanks in advance,
Laura

On Dec 19, 12:46*pm, musicloverlch <lho... (AT) gmail (DOT) com> wrote:
Maybe this will get you started. Drop the following code in a Module.

In your form's code module enter
ExportFilteredTest Me.Name
to call the code. This will create an Excel file called Text.Xlsx

Public Sub ExportFilteredTest(frm As Form)
Dim q As QueryDef
Dim s As String
Dim d As Database

On Error GoTo 0
On Error Resume Next

Set d = CurrentDb
Set q = d.QueryDefs("MyRecordSource")

'get rid of existing queries
DoCmd.DeleteObject acQuery, "MyRecordSource"
DoCmd.DeleteObject acQuery, "MyRecordSourceResult"

'recreate the queries that becomes the Excel recordsource
s = frm.RecordSource
If Left(s, 7) <> "Select " Then
'the form;s recordsource is a table. Make it into a query
s = "Select " & s & ".* From " & s
End If
Set q = d.CreateQueryDef("MyRecordSource", s)

'next, create a query with the recordsource's filters and sorts
s = "Select MyRecordSource.* From MyRecordSource "
If frm.FilterOn Then
'now add the filter
s = s & "Where " & frm.Filter
End If
If frm.OrderByOn Then
'now add the sort order
s = s & " Order By " & frm.Filter
End If
Set q = d.CreateQueryDef("MyRecordSourceResult", s)
d.close
set d = Nothing

On Error GoTo 0 'if you want, turn on error checking here.
Modify result folder/file name
DoCmd.TransferSpreadsheet acExport, , "MyRecordSourceResult", "C:
\Test\Test.xlsx", True

Msgbox "Done exporting"
End Sub

OMG!!! It works like a charm!!!

Thank you soooo much!

Eternally grateful,

Laura





On Dec 20 2011, 3:15*pm, Patrick Finucane
<patrickfinucan... (AT) gmail (DOT) com> wrote:

On Jan 12, 11:22*am, musicloverlch <lho... (AT) gmail (DOT) com> wrote:
In my OrderBy code change
s = s & " Order By " & frm.Filter
to
s = s & " Order By " & frm.OrderBy