Macro to query based off multiple names in a field and export multiple spreadsheets

[devirecui]

Hello, I am using Access 2007 and need a macro that will create multiple tables with each name in the field "Collector" and export it to a folder with each collector's name and the current date.

So for example,

If I had Collectors: John Smith, Mary Smith, Joe Schmo, and Larry Field, it would create 4 different spreadsheets with the tables that were assigned to them.

Is this possible or will I have to make a query for each one and a macro assigned to each creating a spreadsheet for each? Keep in mind the collectors are constantly coming and going.

Let me know if you have any questions.

[CJ_London]

perfectly possible - but you should not need to create multiple tables, just use queries to collated the data you want

Assuming you are running code from the access side, you can use transferspreadsheet functionality to export your queries to excel to a file name of your choice. An alternative, or if you want to format the worksheet(s) is to use the Microsoft excel object library to create an instance of excel in code which then uses excel objects and functionality such as range, cell, worksheet etc