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

Split One table to multiple Excel files

P: n/a
Jen
Trying to take one table in access and split it into multiple excel
files(using an excel template); and then email based on email
addresses in Table2; Of course, I would like to do all of this with
minimum user-interface...If there is anyone out there that can help...
please feel free to share!:)
PS... Using MSOffice 2000
Example:
Table1

Record# Vendor Field1 Field2 Field3 Field4 Field5
1 12345 450000 $1.00 12 $12.00 10-Nov-2004
2 24689 50000 $3.00 1 $ 3.00 10-Dec-2004
3 12345 808500 $5.00 5 $25.00 10-Dec-2004
4 12345 450000 $1.00 12 $12.00 10-Nov-2004
..
..
n 56798 12000 $10.00 1 $10.00 15-Mar-2005

Result I want in an Excel spreadsheets:
Spreadsheet 1 File name: Vendor_12345_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
12345 450000 $1.00 12 $12.00 10-Nov-2004
12345 808500 $5.00 5 $25.00 10-Dec-2004
12345 450000 $1.00 12 $12.00 10-Nov-2004

Spreadsheet2 File name: Vendor_24689_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
24689 50000 $3.00 1 $ 3.00 10-Dec-2004
Spreadsheet3 File name: Vendor_56798_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
56798 12000 $10.00 1 $10.00 15-Mar-2005

Table2
Vendor# Contact Email
12345 Smith, Joe jo******@xxxx.com
24689 Doe, Jane ja*****@xxxx.com
56798 Help, Me he****@xxxx.com
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
jc*****@celestica.com (Jen) wrote in message news:<93**************************@posting.google. com>...
Trying to take one table in access and split it into multiple excel
files(using an excel template); and then email based on email
addresses in Table2; Of course, I would like to do all of this with
minimum user-interface...If there is anyone out there that can help...
please feel free to share!:)
PS... Using MSOffice 2000
Example:
Table1

Record# Vendor Field1 Field2 Field3 Field4 Field5
1 12345 450000 $1.00 12 $12.00 10-Nov-2004
2 24689 50000 $3.00 1 $ 3.00 10-Dec-2004
3 12345 808500 $5.00 5 $25.00 10-Dec-2004
4 12345 450000 $1.00 12 $12.00 10-Nov-2004
.
.
n 56798 12000 $10.00 1 $10.00 15-Mar-2005

Result I want in an Excel spreadsheets:
Spreadsheet 1 File name: Vendor_12345_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
12345 450000 $1.00 12 $12.00 10-Nov-2004
12345 808500 $5.00 5 $25.00 10-Dec-2004
12345 450000 $1.00 12 $12.00 10-Nov-2004

Spreadsheet2 File name: Vendor_24689_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
24689 50000 $3.00 1 $ 3.00 10-Dec-2004
Spreadsheet3 File name: Vendor_56798_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
56798 12000 $10.00 1 $10.00 15-Mar-2005

Table2
Vendor# Contact Email
12345 Smith, Joe jo******@xxxx.com
24689 Doe, Jane ja*****@xxxx.com
56798 Help, Me he****@xxxx.com

Start by reading this article:

http://www.mvps.org/access/modules/mdl0035.htm

This is ONE of several ways to put your data into Excel. If you want
others, I'm sure other people have different methods. I'm sure Rich
P, who usually hangs out in the SQL Server NG will have some good
ideas. He generally uses VBA, but it gives you a LOT more control
over what's going on - like filenames etc. But in your case it looks
like VendorName & "_" & SomeID & Day(Date)..., in other words VERY
easy to automate.

if you wanted to output the files only to e-mail them, you could use
SendObject, but it does have its limitations.

So you want to do something like (Anybody feel free to correct/augment
whatever):
1. open a recordset based on a SQL statement on your addresses table,
call ir rs Recipients.
2. use that as criteria for another recordset, say rsData.
3. use the data from rsRecipients to filter rsData. If you use ADO,
you can pass the recordset stuff directly to an Excel file/template.
(This is where RichP could take over...)

Okay, so now you have your data from Access and into Excel. (I'll
assume that anyway.)

So now all you have to do is automate your e-mail. Dmitri Furman has
code that automates GroupWise... best bet, check Tony Toew's page on
how to do all this good stuff. Can't remember his URL off the top of
my head, but he has zillions of postings here, and it's in his sig.
Either that or check the MVPs section of AccessWeb (open the url I
gave you earlier).

You basically automate your favorite e-mail client and then create a
new message, insert Recipient, Subject, BodyText, and Attachment info,
and Send. If you don't like mucking with the Outlook object model, at
least there used to be a library available from MS, called CDONTS,
which is a wrapper class that simplifies automating Outlook. Don't
know what it's morphed into now, but that greatly simplifies things.
You just pass the information into this wrapper class and it takes
care of the automation internally. Or you could use BLAT. Ask Steve
Jorgensen about that, as I know he uses that.

Hope that gives you some options. If I were you, I would search the
NG for the names etc I gave you. I know for sure all the answers are
here, because I've read them over the years. All you have to do is
find them. Happy Hunting!

HTH,
Pieter
Nov 13 '05 #2

P: n/a
dog
There are many ways you could do this but what I who do first of all
it create a single query in access which holds all you information.
There would have to be a relationship between your 2 tables so that
you could do this (i.e. Vendor ID)

Once you have your query, set a parameter on the Vendor ID (id use a
QueryDef in your code) and use a Do Until Loop in your code which will
loop through all the unique Vendors and their related information.

You need to set a reference in your code to the Excel Object library,
declare the excel object in your module, and inside your code
everytime you loop through each set of records, you need to open your
template file, use the CopyFromRecordset or Offset methods to get the
data in there, and save the file.

The user need only click a button for it to all be done automatically.

Dog

jc*****@celestica.com (Jen) wrote in message news:<93**************************@posting.google. com>...
Trying to take one table in access and split it into multiple excel
files(using an excel template); and then email based on email
addresses in Table2; Of course, I would like to do all of this with
minimum user-interface...If there is anyone out there that can help...
please feel free to share!:)
PS... Using MSOffice 2000
Example:
Table1

Record# Vendor Field1 Field2 Field3 Field4 Field5
1 12345 450000 $1.00 12 $12.00 10-Nov-2004
2 24689 50000 $3.00 1 $ 3.00 10-Dec-2004
3 12345 808500 $5.00 5 $25.00 10-Dec-2004
4 12345 450000 $1.00 12 $12.00 10-Nov-2004
.
.
n 56798 12000 $10.00 1 $10.00 15-Mar-2005

Result I want in an Excel spreadsheets:
Spreadsheet 1 File name: Vendor_12345_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
12345 450000 $1.00 12 $12.00 10-Nov-2004
12345 808500 $5.00 5 $25.00 10-Dec-2004
12345 450000 $1.00 12 $12.00 10-Nov-2004

Spreadsheet2 File name: Vendor_24689_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
24689 50000 $3.00 1 $ 3.00 10-Dec-2004
Spreadsheet3 File name: Vendor_56798_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
56798 12000 $10.00 1 $10.00 15-Mar-2005

Table2
Vendor# Contact Email
12345 Smith, Joe jo******@xxxx.com
24689 Doe, Jane ja*****@xxxx.com
56798 Help, Me he****@xxxx.com

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.