473,890 Members | 1,825 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Split One table to multiple Excel files

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.c om
24689 Doe, Jane ja*****@xxxx.co m
56798 Help, Me he****@xxxx.com
Nov 13 '05 #1
2 6688
jc*****@celesti ca.com (Jen) wrote in message news:<93******* *************** ****@posting.go ogle.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.c om
24689 Doe, Jane ja*****@xxxx.co m
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
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 CopyFromRecords et 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*****@celesti ca.com (Jen) wrote in message news:<93******* *************** ****@posting.go ogle.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.c om
24689 Doe, Jane ja*****@xxxx.co m
56798 Help, Me he****@xxxx.com

Nov 13 '05 #3

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

Similar topics

0
2429
by: kumu | last post by:
How to Split a XML file to multiple small XML Files Hello, I am trying to split a XML file to multiple small xml files in vb.net and am trying to get the best possible approach to this. Any help on this will be great... Sample example on what I am trying to do... Thanks Kumu Source XML document
1
2700
by: mia456789 | last post by:
I hv a mysql db in my RH linux , there is a very large table in the db , the file size is about 2G , how can I split the file into two files - two files physically and one file logically ? is there any tools to do it ? thx
0
1678
by: Eliezer Figueroa | last post by:
Managing Multiple Excel incoming files? I have this situation. I have a client which have several locations they work primary with excel forms and they are thinking in doing reports with them. What they want is to import those Excel files into SQL Server for later using crystal reports or maybe asp.net for reporting. Those files will come every week from diferent locations then go to SQL Server and then they should be stored in a folder...
1
3366
by: poohnie08 | last post by:
i have a excel spreadsheet showing staff name, date,work hour, ot hour, slot1, slot2, slot3, slot4 and others). The "()" will keep repeating from day 1 until end of month. eg in excel spreadsheet, ============================================================================== A1 |A2 A3 A4 A5 A6 A7 A8 |A9 A10 A11 | 01/02/04 |02/02/04 StaffName |Work Hr OT Hr Slot1...
6
1970
by: Stan | last post by:
I am working on a database in ACCESS 2003. This is a simple DB with only one table. I have split the DB so I can upgrade and debug the front end before installing on my clients' computer. I used the ACCESS splitter utility and everything appeared to go OK. If I run "Copy of DB_be.mdb" I see only the table, not the Queries or Forms. When I run the front end "Copy of DB.mdb" I see the Queries and Forms. but, I also see the Table. If I...
7
12086
by: TG | last post by:
hi! I am trying to create a sql server table from an excel sheet. Here is the code I have: 'This procedure the xlsx file and dumps it to a table in SQL Server
4
1632
by: Twilight | last post by:
There is a table tbl1,with several fields, one of them is City, with the value varies from city1 to city30. I want to use Access Marco to automatically using function transferspreadsheet to copy this tbl1 into 30 excel sheets, by the value of city. For eg, select * from tbl1 where city value=city1 , then put the selected records into excel file “\…\…\city1.xls”, Thus generates 30 excel files. Is there a simple way to achieve this, avoid...
7
8437
by: franc sutherland | last post by:
Hi everyone, I am using Access 2003. I have a database with a table in it which is linked to an excel spreadsheet. When I install the database on someone else's system, the pathname to the excel file is different. Howwever, as it is in runtime on their system, I can't update the link using the Linked Table Manager. Is there a way of using a public constant to store a path which can be altered in advance and then combined with a...
3
5948
by: stephen | last post by:
Hi, I have 5 excel files and they have multiple sheets. I have to read (say sheet 3) of each of the 5 excel files and consolidate them into one. what's the best way to achieve this. if someone can point me to an article or sample that will be real helpful. I am able to read a excel file and then do whatever I want but how to have 5 excel files open and then read and then consolidate. I am a bit confused here.
0
9975
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...
0
9812
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11212
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9614
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
8004
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
7154
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
5832
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...
1
4655
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
2
4255
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.