Auto Splitting a query using VB code

[dfaulkner1]

Hi

I was hoping somebody could help me, I'm struggling to get my database to auto split up a Query by a certain Column and automatically get it to export into separate Excel files.

I have got the export sorted, currently set to export the whole query, but i'm struggling to do the splitting up, does anybody have a piece of script they know works for this?

Thanks
Dan

[June7]

Is it always the same fields for export - same 'split'? What export method are you using? I expect you will need two queries.

[dfaulkner1]

Here is an example of the data I'm trying to export:

Led Acc
1 1blah
1 1okhi
4 4high
5 5cmd1

What I'm trying to do is get it split up by the "Led" field, so you would have a .xlsx file with just the led 1 accounts on, another with the just the led 4 etc.

Here is what I'm currently using to export all into just one file
DoCmd.OutputTo acOutputQuery, "Newly_created_accounts", acFormatXLSX, "\\buzz2\Office on Buzz2\Newonlineusers\test1.xlsx"
DoCmd.Close acQuery, "Newly_created_accounts", acSaveNo

[June7]

Then you need to apply filter criteria. Options for modifying query filter criteria:

1. dynamic parameters in query - either popups (I NEVER use) or reference to controls on form

2. export a report based on query - can be dynamic parameterized query http://www.datapigtechnologies.com/f...mtoreport.html or apply filter criteria to report when it is opened using the WHERE CONDITION argument of OpenReport method

3. VBA code that modifies query using QueryDefs

How many Led values are there? I expect will need to open a recordset of the Led values, loop through recordset, somehow set the query filter parameter with value, export query, move to next Led record, repeat.