473,727 Members | 2,035 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

code to export to multiple Excel worksheets within single workbook

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
4 8709
pa*******@gmail .com wrote in
news:11******** **************@ j33g2000cwa.goo glegroups.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
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******@sympa tico.ca> wrote in message
news:Xn******** **************@ 207.35.177.135. ..
pa*******@gmail .com wrote in
news:11******** **************@ j33g2000cwa.goo glegroups.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
I told you to go away.

Don't go away mad.

Just go away.

"PCD" <no***@email.co m> wrote in
news:0f******** **********@news read3.news.atl. earthlink.net:

the babblings of a psychopathic idi10t

--
Bob Quintal

PA is y I've altered my email address.
Mar 28 '06 #4
PCD
Tsk, Tsk, Tsk, Boob!!!

INDEED!
"Bob Quintal" <rq******@sympa tico.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.co m> wrote in
news:0f******** **********@news read3.news.atl. earthlink.net:

the babblings of a psychopathic idi10t

--
Bob Quintal

PA is y I've altered my email address.

Mar 28 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2686
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet. mainly i need to export data to multiple worksheets. it is very urgent to us. thanks Regards gaffar.
0
1623
by: priya0123 | last post by:
Hi, I have written an application where onclick of a button, asp.net page is exported to an excel spreadsheet. But now the client want to have multiple spreadsheets. for example sheet1 has some date, sheet2 has some date. And also each should be given a name. How do I do that? Any help would be appreciated.
1
3896
by: =?Utf-8?B?RGF2ZQ==?= | last post by:
Hi, I'm using ASP.NET 1.1 and trying export a dataset with multiple datatables (each to it's own Excel worksheet.) I've successful bound a DataTable a DataGrid using the code below but I don't know how to create multiple worksheets tabs to correspond to each datatable. Response.ContentType = "application/vnd.ms-excel";
10
1964
by: charvi | last post by:
Can I generate more than 1 Excel sheet when a command button is clicked? For eg I search for 2 names of audit dept and click print command button where records will be printed in Excel. Again if I search for other 2 names and if I click print command button reports will not be printed.and gives error I have to close the program and load the program again. If yes can you tell me how? When I write a code when it executes or second Excel sheet...
3
5566
habby0123
by: habby0123 | last post by:
I am trying to export data to Excel from a query in Access. There is a unique "Sales Region ID" and various "Territory#'s" for each Region. I would like one excel workbook for each region, and a worksheet for each Territory # assigned to that region. I do something similiar with a report that I programmatically convert to a pdf and it works fine, but I'm having a little trouble with the excel export. Can someone give me some advice? (Below is my...
0
1907
by: appu123 | last post by:
How can we copy multiple Excel Sheets into a new Workbook. I was trying to copy using File::Copy, but it does not help as it overwrites the existing details
6
7866
by: shilpag | last post by:
How to export data to multiple excel worksheets using php can anyone help on this please
11
1448
by: Shannon West | last post by:
I have recently acquired the job of updating our Access dB (2000 version) and I am a bit over my head here (my work with Access was limited to entering information into forms or running the reports requested - now they are asking me to update/fix/DESIGN these things!!). The database was designed by some other party many years ago, and has been copied to create new databases every year. This data base if for shipping of Christmas trees, so it...
1
2989
by: Sekhar C | last post by:
I have 100 excel sheets with same field name,with same number of fields,with similar datatype. i want to transfer data from all excel sheets to one Sql database Table using one Package, i am using SQl Server Integration Services 2008 r2 version, Bussiness Intelligence Development Studio and MS SQL Server 2008. please guide me.
0
8891
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
9185
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8103
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6703
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6011
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4521
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4786
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3228
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.