code to export to multiple Excel worksheets within single workbook

[paul.chae@gmail.com]

I have a table in Access with about 3000 records. There are ~60 unique
values in the ID field for the 3000 records. What I would like to do
is automatically generate multiple Excel worksheets within a single
workbook with these records. I would end up with around 500
worksheets, 1 for each unique ID value. I was thinking this could be
done if I have an exported flag column in the table, and I search for
the max (or min) on the ID field, select those, and export those into a
worksheet, and set the flag to yes. Then the process could be run from
the beginning again so that it selects the next set of records for the
max, and exports those.

Does anyone have code that would do this?

[Bob Quintal]

Re: code to export to multiple Excel worksheets within single workbook

paul.chae@gmail .com wrote in
news:1143472461 .438484.197920@ j33g2000cwa.goo glegroups.com:
[color=blue]
> I have a table in Access with about 3000 records. There are
> ~60 unique values in the ID field for the 3000 records. What
> I would like to do is automatically generate multiple Excel
> worksheets within a single workbook with these records. I
> would end up with around 500 worksheets, 1 for each unique ID
> value. I was thinking this could be done if I have an
> exported flag column in the table, and I search for the max
> (or min) on the ID field, select those, and export those into
> a worksheet, and set the flag to yes. Then the process could
> be run from the beginning again so that it selects the next
> set of records for the max, and exports those.
>
> Does anyone have code that would do this?
>[/color]
You'd be a lot better off to export the entire recordset into Excel
and build a pivot table to do your selection.

The question I have is why have 500 separate worksheets?

--
Bob Quintal

PA is y I've altered my email address.

[PCD]

Re: code to export to multiple Excel worksheets within single workbook

Boob ---

<<You'd be a lot better off to export the entire recordset into Excel and
build a pivot table to do your selection.>>
You forgot to tell the OP your patented, WELL DOCUMENTED technique to export
all the records by exporting one field at a time. Or maybe this isn't such a
perfectly correct technique after all!!

<<The question I have is why have 500 separate worksheets?>>
Answer - the OP looked at your previous response to another OP wanting to
export Access records to Excel, followed your recommendation and exported
the records one field at a time!!

Er-rr If there are 3000 records and ~ 60 unique values and the OP wants 1
worksheet for each unique ID, that sounds like 60 worksheets, not 500
worksheets. INDEED, that assumes the OP does not try the patented, WELL
DOCUMENTED technique of exporting one field at a time!!!

INDEED, INDEED, INDEED!!!!


"Bob Quintal" <rquintal@sympa tico.ca> wrote in message
news:Xns9793B67 D55590BQuintal@ 207.35.177.135. ..[color=blue]
> paul.chae@gmail .com wrote in
> news:1143472461 .438484.197920@ j33g2000cwa.goo glegroups.com:
>[color=green]
>> I have a table in Access with about 3000 records. There are
>> ~60 unique values in the ID field for the 3000 records. What
>> I would like to do is automatically generate multiple Excel
>> worksheets within a single workbook with these records. I
>> would end up with around 500 worksheets, 1 for each unique ID
>> value. I was thinking this could be done if I have an
>> exported flag column in the table, and I search for the max
>> (or min) on the ID field, select those, and export those into
>> a worksheet, and set the flag to yes. Then the process could
>> be run from the beginning again so that it selects the next
>> set of records for the max, and exports those.
>>
>> Does anyone have code that would do this?
>>[/color]
> You'd be a lot better off to export the entire recordset into Excel
> and build a pivot table to do your selection.
>
> The question I have is why have 500 separate worksheets?
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.[/color]

[Bob Quintal]

Re: code to export to multiple Excel worksheets within single workbook

I told you to go away.

Don't go away mad.

Just go away.

"PCD" <notmy@email.co m> wrote in
news:0f%Vf.1086 5$k75.9184@news read3.news.atl. earthlink.net:

the babblings of a psychopathic idi10t

--
Bob Quintal

PA is y I've altered my email address.

[PCD]

Re: code to export to multiple Excel worksheets within single workbook

Tsk, Tsk, Tsk, Boob!!!

INDEED!


"Bob Quintal" <rquintal@sympa tico.ca> wrote in message
news:Xns9793C7A A974EABQuintal@ 207.35.177.135. ..[color=blue]
>I told you to go away.
>
> Don't go away mad.
>
> Just go away.
>
> "PCD" <notmy@email.co m> wrote in
> news:0f%Vf.1086 5$k75.9184@news read3.news.atl. earthlink.net:
>
> the babblings of a psychopathic idi10t
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.[/color]