Locate Specific Records from Queries and Export to Excel

[Guest]

Problem: I need to email 100 dept heads revenue data specific to their
departments.

Location of Data: I have 4 queries in Access. Each query was set up to
report on 4 different types of revenue. Each query has a DeptNumber Field.
The DeptNumber field has about 100 unique department numbers, and the total
number of records is about 9,500 in each query.

Ideal Solution: I would like a VBA macro in Access that would export into
excel just the records from the 4 queries that are specific to each
department number, and give the excel file the name of the dept number.

So far I am just able to export to Excel a query or a table, but I am not
able to loop through a query and generate excel files with just the records I
need.

Any help to point me in the right direction would be appreciated. Thank you

 

[Guest]

Hey, I was able to do it. I adapated code from a very useful older posting
from Ken Snell. It works great!! Time for a coffee break now.

 

[Ken Snell \(MVP\)]

Glad the code was helpful!