Exporting MS Access query into multiple excel workbooks

[mrtn]

Hi

I have got an existing query which is exported to excel through DoCmd.Transferspreadsheet.

In simple terms the query looks like below:

Group | Client

Group1 Client1
Group1 Client2
Group1 Client3

Group2 Client4
Group2 Client5
Group2 Client6

etc.

As some of the groups contain large number of clients, I am trying to find the code to help me split groups by pre-specified number of clients and export to excel, as follows:

Group1 - clients 1 to 300.xlsx

Group1 - clients 301 to 600.xlsx

Group2 - clients 1 to 300.xlsx

etc.

Any suggestions would be much appreciated. Many thanks.

 

[mh123]

hm, try the following;
set filters on the query and export those one by one to workbooks or separate sheets in the same workbook if you desire
also probably worth setting a function to rst.recordcount by group to work out how many filtered queries you'll need to export to xls