Auto splitting a Query from Access to separate Excel spreadsheets

[dfaulkner1]

Hi

I am trying to set my database so when I click on a button, it will autosplit a query that I have into separate sheets based on "Ledger".

Here is an example piece of data

Ledger	Account	Account Name	Title	Forename	Surname	Email	Date
1	01TARENV	Tardis Environmental	Mr	John	Jones	jj@aol.com	17/07/2015
1	01STARBE	Starbeach	Ms	Kim	Newton	Kim@starbeach.com	18/07/2015
4	04HOLLA	Holla	Mr	James	Smith	jamessmith@holla.co.uk	22/07/2015
5	05DUMFRE	Dumfree	Mr	Harry	Dean	Hdean@gmail.com	20/07/2015

So in one Excel sheet I would need all Ledger 1's, another sheet I would need all ledger 4's...

I need this automated as there is a lot of data and we will be running this every other day



Any help?

Thanks

[ItsMe]

In order to automate the process you will need to build code to iterate the records and create worksheets for the various Ledger groups. VBA can be used to automate Excel and create a new workbook. As the VBA loops through the records it can determine the value of ledger and create a new worksheet for each value. With each new worksheet, I would use the CopyFromRecordset method to import the data. The iterative statement would dynamically parameterize the query for the CopyFromRecordset method.

https://msdn.microsoft.com/en-us/lib.../ff839240.aspx