472,118 Members | 1,184 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,118 software developers and data experts.

Exporting A Filtered Form To Excel

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.

Expand|Select|Wrap|Line Numbers
  1. Dim xlApp As Object
  2. Dim xlBook As Object
  3. Dim rs As DAO.Recordset
  4. Dim sql As String
  5. Dim i As Integer
  7. Set xlApp = CreateObject("Excel.Application")
  8. Set xlBook = xlApp.Workbooks.Add
  10. sql = Forms("frmProjectEDISearch").Form.RecordSource 'Your record source if not a subform
  11. 'Set rs = CurrentDb.
  13. For i = 1 To rs.Fields.Count
  14. xlBook.Sheets(1).Cells(1, i) = rs.Fields(i - 1).Name 'Write Field names to Excel
  15. Next i
  16. xlBook.Sheets(1).Cells(2, 1).CopyFromRecordset rs 'Import the recordset data through Excel
  18. ' You can add whatever other formatting you want by running Excel VBA throught the xlApp object
  20. xlApp.Visible = True
  22. Set xlApp = Nothing
  23. Set xlBook = Nothing
  24. 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.


Nov 16 '07 #1
2 18925
1,923 Expert 1GB
it looks like you have already gotten your answer from allen browne.

Nov 18 '07 #2
try sending data to a temp table using your search query before exporting it to excel.
Nov 20 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by WindAndWaves | last post: by
2 posts views Thread by Michael | last post: by
1 post views Thread by Mustufa Baig | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.