Export a query to multiple Excel sheets?

[pbibler]

I have a query which returns about 10,000 rows. One of the columns is dept. My customer would like me to create one Excel sheet per value of dept (all in the same .xls file, about 30 of them). It's easy enough, though tedious, to copy and paste, or create multiple queries, but I would like to use a macro or some other automated way if possible. Unfortunately, I don't know any VB.

I have figured out how to open the query and apply filters, and do that multiple times, but that's as far as I've gotten. Any help is appreciated.

Regards,

Paul B.

 

[DougP]

Just as an example did you try to create a report grouped by Dept and then Export that to Excel?

DougP, MCP, A+

 

[pbibler]

Hi Doug,

Exporting really isn't the problem, it's exporting to multiple sheets. I've been able to export the whole thing to one sheet pretty easily. I need some iterative way to export, I guess.

 

[CautionMP]

pbibler,
Here is a concept that may work for you.
thread181-1274220 : Help Exporting from Access to Excel

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[SkipVought]

Hi,

WHY multiple sheets in Excel?

Why not use the AutoFilter to select DEPT on ONE SHEET.

Skip,
[sub]
[red][/red]
[/sub]

 

[pbibler]

CautionMP, thanks, I will check this out. I haven't used VB before, but I'm sure I can get someone to assist.

SkipVought, that was the first thing I said to my customer, but in the end, I have to satisfy them. I'll make sure to pass on my pain the them, though ;-)

 

[SkipVought]

I'd macro record adding a QueryTable via Data/Get External Data/New Database Query....

Then use that code in a loop to insert sheets and add query table with modified SQL CommandText to select the subset of data you need on each sheet.

Skip,
[sub]
[red][/red]
[/sub]