469,626 Members | 1,070 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

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
  6.  
  7. Set xlApp = CreateObject("Excel.Application")
  8. Set xlBook = xlApp.Workbooks.Add
  9.  
  10. sql = Forms("frmProjectEDISearch").Form.RecordSource 'Your record source if not a subform
  11. 'Set rs = CurrentDb.
  12.  
  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
  17.  
  18. ' You can add whatever other formatting you want by running Excel VBA throught the xlApp object
  19.  
  20. xlApp.Visible = True
  21.  
  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.

Thanks.

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

http://groups.google.com/group/micro...6c06821e5648c6
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
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.