Produce xls files from a query for individual entries in a given field

[scubaninjanz@gmail.com]

I am looking for a method to quickly extract multiple excel files to a predefined location for each unique field in a data query. Ideally with a single click or maybe click and then prompt for file location.

For example:

Field A	Field B	Field C
A	Cat	Apple
A	Dog	Orange
B	Horse	Banana
B	Mouse	Pineapple
B	Dog	Pear
C	Elephant	Apple
C	Cat	Mandarin
D	Chipmunk	Apple
D	Monkey	Banana

From this file produce for individual excel files saved to a given location for each unique entry in Field A.

A.XLS

Field A	Field B	Field C
A	Cat	Apple
A	Dog	Orange

B.XLS

Field A	Field B	Field C
B	Horse	Banana
B	Mouse	Pineapple
B	Dog	Pear

C.XLS

Field A	Field B	Field C
C	Elephant	Apple
C	Cat	Mandarin

D.XLS

Field A	Field B	Field C
D	Chipmunk	Apple
D	Monkey	Banana

[ranman256]

make a form, with a list box, the list shows all the unique recs of Field A
A
B
C
D

the query to send, qsMyRpt, will look at what is selected in the list ,
select * from table where [fieldA]=forms!myForm!lstBox

a button to run the code to scan the list and export,

sub btnMake_click()
dim i as integer
dim sUnit as string 
dim vFile

for i = 0 to lstBox.listcount - 1
   sUnit = lstBox.ItemData(i)   'get next item in list
   lstBox = sUnit              'set list to that item for the query to read

   vFile = "c:\temp\" & sUnit & ".xls"

   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qsMyRpt", vFile, True, sUnit
next
end sub