Generating spreadsheets from Access

[ANDRE726]

Greetings. I have an Access table with about 8,000 records. The key field is Region. The 8,000 records are distributed over 50 regions. What I would like to do is to generate 50 spreadsheets, programmatically, one for each region. How would I go about doing this in code? Any help would be appreciated.

[Lysander]

Greetings. I have an Access table with about 8,000 records. The key field is Region. The 8,000 records are distributed over 50 regions. What I would like to do is to generate 50 spreadsheets, programmatically, one for each region. How would I go about doing this in code? Any help would be appreciated.

I used the following code to do a similar thing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "qryCombinedWithChildren", "z:\bissau\CombinedWithChildren.xls", True
 

What you could do is replace the hard coded query name and file name with variables and then loop through your regions running this code with different queries and file names.

Better still, you could use the same query, changing the region filter on it each time, but I cant remember how to do this of the top of my head.