Hi,

I want a user to be able to open a query (subform with the query as recordsource is OK), do some sort/filtering on it and export the resultant recordset to Excel with vba. I can't figure out how to export the filtered recordset without saving it first, which is something I'd rather avoid if possible. The filtering will be done using the built in capabilities of Access 2007.

Thank you.

This post has been edited by expatriate: Dec 6 2011, 07:07 AM

You would just do this through a querydef. e.g. below.



This is just a basic example with 2 combo-boxes being used for criteria. In this example, your main query that has all the fields that you want would be called "MyQueryMain". You would then create another query in this example "MyQDF". It does not matter what you put in this query, you just need to have a query with that name. This will create a query with the filtered results and export it to Excel. HTH.

Thanks for the response. Maybe I am missing something. Like I said, the filtering will be done from the built in context filtering in Access 2007, not from external sources like cbos. How do I build the SQL string?

Oh, you're using the built in filters that are available in the data grid? I'm not sure how you would do that or if it's even possible. Building your own unbound text boxes, combo boxes, list-boxes etc. to allow users a wide-range of filtering possibilities is pretty easy to do if you want to go that route.

No, I don't want to do it that way because both I and the client agree the built in functionality is superior. It does work opening the query, applying the filters and using Export on the ribbon so I am hoping there is a way with VBA as I would like to do some customization as to exported file name and location.

Well, I would disagree with you on that one for a number of reasons. One being, I don't like giving users that much access where they can easily select all the records and delete them accidentally. Also, with the forms, I find it's more efficient because the users are not having to right click on each column and selecting check-boxes for example, they can just simply click on multiple values in a list-box. Also, you can do a lot of customizations this way. You can segregate data into separate tabs, files, allow the users to name the file, select the file save location, e-mail as an attachment, etc. etc, etc. I prefer this way. Just my opinion for what it's worth.

If you're allowing them the access like this. You could just have them simply select all records, copy and paste into a spreadsheet or even use the export function as you said. Anyway, good luck with your project.

Solved - well, reasonably well.
1) Export using the ribbon, save the export spec.
2 )Create a simple function calling the spec:

Function V1()
On Error Resume Next
Kill CurrentProject.Path & "\teachers view.xlsx"
On Error GoTo procError
DoCmd.RunSavedImportExport "export-qV4"
procExit:
Exit Function

procError:
Select Case Err.Number
Case Else
MsgBox "Error #" & Err.Number & ": " & Err.Description, _
vbOKOnly + vbCritical, GTITLE
End Select
Resume procExit
End Function
3) assign the function to macro and ideally create a new ribbon tab with a button to call it.
5) A simple form calls the query from a button - of course use a non-updateable query as this is just to export data for further processing.
6) User runs the query, does the filtering, calls the macro. The query will be output to the same dir as the db, but the above function could copy it elsewhere, email it or whatever.

Glad you got it worked out. I don't work with the built-in filters a whole lot but I assumed, if you exported filtered results that it would not export as the filtered results. So, I tried this on my end and it's not returning the filters that I'm setting in the datasheet view of the query. It's returning all the records that meet the query's criteria, which makes sense and I figured it would do. How are you exporting it where it's returning the filtered results like that?

Hmm. I'm not doing anything special. I open the query (not a form based on the query, the query itself), filter it using the context menu (or not) go to the Export tab on the ribbon, and export saving the spec.
Then if I reopen, use different filtering, recall the spec like so:

on error resume next
Kill CurrentProject.Path & "\teachers view.xlsx"
On Error GoTo procError
DoCmd.RunSavedImportExport "Export_Teachers_View"

It does export using the current filtering.

That's odd. On my end. That doesen't do that. I've tried it on different computers too. It's just exporting what the actual output is and ignoring the filtering, which is kind've what I would expect. Anyway, not sure why it's actually returning the filtered results on your spreadsheet like that but glad you got it working.

That's odd. On my end. That doesen't do that. I've tried it on different computers too. It's just exporting what the actual output is and ignoring the filtering, which is kind've what I would expect. Anyway, not sure why it's actually returning the filtered results on your spreadsheet like that but glad you got it working.

I was a bit surprised myself at first, but it is definitely working as described.

Now I don't think it is surprising. The filters aren't stored in the output spec - I'm mean I'm assuming that - there doesn't seem to be any place to store them and it doesn't make sense to me that they would be. Then the query is open, returning a filtered recordset. The runSavedImportExport command exports the query as it is, which is filtered.

What happens if you open a query, filter it with the context filtering (nothing else), and export it from the ribbon on the External Data tab?

This post has been edited by expatriate: Dec 7 2011, 06:59 AM

That's what I did. I filtered and saved it and then exported from the toolbar even with a saved spec, which really should not make a difference as far as what information is being exported. This is pretty much doing exactly what your code is doing. Not that I would ever have a need for this but bugs me why it works on your end and it's not working on mine.

Saved it how? I did not save anything but the spec.

There's got to be something you are doing differently. Does the query have any parameters?

I got it. It has be exported with "Formatting" or it will not work. I'll stick to the form filters. I have a function that I use for everything. It reads all the fields and each field's data type in a given record source. All I have to do is just drop any type of control, List Box, Combo Box, Text box ranges, etc. and give them the same name as the field name I want to filter on and that's it. The code automatically peforms the filtering, which can be applied to exports, reports, querydefs, word merges, etc. etc.

Anyway, glad you got it what you need it doing. Good luck with your project.

Ah, good to know!

Maybe, you'll find this link useful on exporting.

http://office.microsoft.com/en-us/access-h...A001224122.aspx