By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,914 Members | 1,706 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,914 IT Pros & Developers. It's quick & easy.

code to export to multiple Excel worksheets within single workbook

P: n/a
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?

Mar 27 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
pa*******@gmail.com wrote in
news:11**********************@j33g2000cwa.googlegr oups.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?

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.
Mar 27 '06 #2

P: n/a
PCD
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" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
pa*******@gmail.com wrote in
news:11**********************@j33g2000cwa.googlegr oups.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?

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.

Mar 28 '06 #3

P: n/a
I told you to go away.

Don't go away mad.

Just go away.

"PCD" <no***@email.com> wrote in
news:0f******************@newsread3.news.atl.earth link.net:

the babblings of a psychopathic idi10t

--
Bob Quintal

PA is y I've altered my email address.
Mar 28 '06 #4

P: n/a
PCD
Tsk, Tsk, Tsk, Boob!!!

INDEED!
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
I told you to go away.

Don't go away mad.

Just go away.

"PCD" <no***@email.com> wrote in
news:0f******************@newsread3.news.atl.earth link.net:

the babblings of a psychopathic idi10t

--
Bob Quintal

PA is y I've altered my email address.

Mar 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.