How to export data whichever way user filters it on the form

[kattatonic1]

Hello,



I have a form whose record source is a query combining employee data, QRY-EMPLOYEES.

User will filter and sort this data mega-multiple ways on this form. A simple example: filtered for a single department and location, sorted by name.

I have created an export button whose event on Click is to export to Excel. Simplest macro works great. Hooray! However, it ignores the user's filtering and sorting and just exports the entire record source QRY-EMPLOYEES.

I'm thinking there's a simple way to say export just the data filtered, and as sorted. I couldn't possibly make a query for every single combination.

Any help appreciated!

Kay from Toronto

[rpeare]

You will likely have to build a SQL statement on the fly or have a separate query where the criteria are set by whatever is on your form and export the separate query/SQL statement. I suspect it would rather be the former than the latter but I don't know the complexity of your query or the data types.

[kattatonic1]

I have another clue. If I just use the command from the ribbon [ External Data tab > Export Group > Excel ], it does export just what is filtered! So wouldn't that mean that there must be a simple VB statement for it? I've been searching for samples online. I think this must be a common request.

[June7]

That opens the Import/Export wizard and user can make choices.

Open the wizard programmatically with:

DoCmd.RunCommand acCmdExportExcel

If you don't want the wizard, will have to be along what rpeare describes using TransferSpreadsheet method or other more complicated code.

[kattatonic1]

Thank you both, rpeare and June7! I think opening the wizard programmitically will work for my user group.

I have been trying to avoid coding since I haven't coded for years and I'm building fairly simple dbs. This has come up a few times. I think I need a list of how to execute all the ribbon commands programmatically. I will search for one, but I don't suppose there's one somewhere on the forum and I just haven't stumbled across it yet?

With regards, Kay

[ItsMe]

I will search for one, but I don't suppose there's one somewhere on the forum and I just haven't stumbled across it yet?

With regards, Kay

Docmd.RunCommand

acCommand Enumeration
http://msdn.microsoft.com/en-us/libr.../ff821354.aspx

Also, if you want to get the current recordset of the form you can use RecordsetClone. This will include only the filtered results.

something like
dim rs as dao.recordset
set rs = me.recordsetclone

additionally, you can get the where clause from a form's filter by (I think)
dim strFilter as string
strFilter = Me.filter

You can then pass the strFilter to a docmd.openreport where criteria

[kattatonic1]

Hey ItsMe,

That's perfect. I'll bookmark it and this also. I love this forum and you ppl!

Kay