Exporting A Filtered Form To Excel

[atlbearcat]

Here's one that's been bugging me for about a week now...

I have a form that allows users to filter records, simple enough. But I want to give them the option to export the filtered records to Excel. I don't want to use the docmd.outputTo due to it won't filter the records, it puts all of the records in the file. I've looked around and found some code (actually that I'm already using), but the problem is that it outputs EVERYTHING on the form. My goal is to output only the fields that are on the form.

I found another bit of code (following) that does what I need it to do in the way of only exporting the data and field names, but I've inadvertantly deleted part of one line (the Set rs=currentDB) and commented it out.

Dim xlApp As Object
Dim xlBook As Object
Dim rs As DAO.Recordset
Dim sql As String
Dim i As Integer
 
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
 
sql = Forms("frmProjectEDISearch").Form.RecordSource 'Your record source if not a subform
'Set rs = CurrentDb.
 
For i = 1 To rs.Fields.Count
xlBook.Sheets(1).Cells(1, i) = rs.Fields(i - 1).Name 'Write Field names to Excel
Next i
xlBook.Sheets(1).Cells(2, 1).CopyFromRecordset rs 'Import the recordset data through Excel
 
' You can add whatever other formatting you want by running Excel VBA throught the xlApp object
 
xlApp.Visible = True
 
Set xlApp = Nothing
Set xlBook = Nothing
Set rs = Nothing

I know that the rs statement is for the Record Source. But...I'd like to somehow have that statment pull in the filtered data. Most everything I've seen with the OpenRecordset command uses a table or query. The filter is on the form and changes dynamically based on what's entered.

Any help would be greatly appreciated.

Thanks.

Jeff

[puppydogbuddy]

Jeff,
it looks like you have already gotten your answer from allen browne.

http://groups.google.com/group/micro...6c06821e5648c6

[chuch0117291]

try sending data to a temp table using your search query before exporting it to excel.