Export one query to multiple Excel files

[bliffer]

I can't seem to find much to help me out on this at all but here's my goal:



I have one query that I need to export to multiple Excel files. What I would like to do is create a table that has one column that would hold the criteria for the WHERE clause of my query. Then, I would like to automatically export one Excel file for each entry in the criteria table.

Basically what I have is a giant file that has 400+ values in a certain column. I've been asked if I could create one spreadsheet with each of the 400 values. That would be a bear to do manually!

Any ideas?

[June7]

The 400+ values are the filter criteria? Post an example.

Several ways to accomplish. Following is possibly the simplest. Open a form bound to the table with the 400+ values, code can move to each record on form. Query object refers to textbox bound to 400+ field as a filter parameter. Code exports query object with TransferSpreadsheet method.

[bliffer]

The 400+ values are the filter criteria? Post an example.

Several ways to accomplish. Following is possibly the simplest. Open a form bound to the table with the 400+ values, code can move to each record on form. Query object refers to textbox bound to 400+ field as a filter parameter. Code exports query object with TransferSpreadsheet method.

Thanks for the response!

The query would have a clause like "...WHERE CHAINCODE = '[Value]'"

The table would hold all of the values and the automation would run one query per value on the table and then export to an Excel file and name it something like "[CHAINCODEValue]-GenericFileName.xlsx"

My problem is that I am horrible at looping functions so if anyone knows of a tutorial that shows how to go about this I would appreciate it.

[June7]

So the criteria is not applied to the same field for each case? That does complicate. Other approaches:

1. code modifies query object using QueryDefs collection

2. code writes records to a 'temp' table and then export the table

3. code opens Excel object and manipulates to add data one value at a time then save the workbook

Any solution requires looping code. There are many tutorials to learn the many facets of programming but no one source will tell you how to do EXACTLY what is required for your situation..