Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / August 2006

Tip: Looking for answers? Try searching our database.

Mail Merge / Page break on record change

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Scott - 14 Aug 2006 14:07 GMT
Trying to use the mail merge feature in Word 2003 to merge data from an excel
file, but I need the merge to break when there is a change in the data - a
data file would look like this.

Custid    Name    Address1   Address2   Dept      Desc      2006 Prod      
2005 Prod
100        Test     123 MAIN   TEST, GA    901     AUTO     1568.90          
500.95
100        Test     123 MAIN   TEST, GA    903     PROP     485.00          
0.00
100        Test     123 MAIN   TEST, GA    932     P&C       3468.50        
300.00
200        ABC      XYZ MAIN   123, GA      908     PEST      2589.00        
 0.00
200        ABC      XYZ MAIN   123, GA      933     BOAT      589.00        
297.76
200        ABC      XYZ MAIN   123, GA      901     AUTO      459.00        
678.09
200        ABC      XYZ MAIN   123, GA      923     HOME      420.00        
 2000.00

I want the mail merge to page break when the Custid changes, but before it
breaks, I want to give a grand total for the 2006Prod and 2005Prod Columns.  
Also just print the Custid, Name, Address1, Address2 once on the page.  This
would later be printed and folded to be mailed in a window envelop.  So the
net effect would look something like this:

100        Test     123 MAIN   TEST, GA    

901     AUTO     1568.90          500.95
903     PROP     485.00            0.00
932     P&C       3468.50          300.00

----------- page break -----------------

200        ABC      XYZ MAIN   123, GA      

908     PEST      2589.00          0.00
933     BOAT      589.00          297.76
901     AUTO      459.00          678.09
923     HOME      420.00           2000.00

Hope this makes sense.  I need help really really bad - Thanks in advance
for your time.
Graham Mayor - 14 Aug 2006 14:51 GMT
See How to use mail merge to create a list sorted by category in Word 2002 -
http://support.microsoft.com/?kbid=294686

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Trying to use the mail merge feature in Word 2003 to merge data from
> an excel file, but I need the merge to break when there is a change
[quoted text clipped - 41 lines]
> Hope this makes sense.  I need help really really bad - Thanks in
> advance for your time.
Scott - 14 Aug 2006 14:58 GMT
thats what I am trying as we speak, but I cannot seem to make that work
either.  do you have a sample word document i could use as a template to
figure this out?

> See How to use mail merge to create a list sorted by category in Word 2002 -
> http://support.microsoft.com/?kbid=294686
[quoted text clipped - 44 lines]
> > Hope this makes sense.  I need help really really bad - Thanks in
> > advance for your time.
Doug Robbins - Word MVP - 14 Aug 2006 22:36 GMT
See the "Group Multiple items for a single condition" item on fellow MVP
Cindy Meister's website at

http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#DBPic

Or take a look at the following Knowledge Base Article

http://support.microsoft.com/default.aspx?scid=kb;en-us;211303

http://www.knowhow.com/Guides/CompoundMerges/CompoundMerge.htm

Or, if you create a Catalog (on in Word XP and later, it's called Directory)
type mailmerge main document with the mergefields in the cells of a one row
table in the mailmerge main document with the keyfield in the first cell in
the row and then execute that merge to a new document and then run the
following macro, it will create separate tables with the records for each
key field in them.  With a bit of further development, you should be able to
get it to do what you want.

' Macro to create multiple items per condition in separate tables from a
directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
   Set tcat = ttab.Cell(j, 1).Range
   tcat.End = tcat.End - 1
   Set scat = stab.Cell(i, 1).Range
   scat.End = scat.End - 1
   If scat <> tcat Then
       ttab.Rows.Add
       j = ttab.Rows.Count
       ttab.Cell(j, 1).Range = scat
       ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True
       ttab.Rows.Add
       ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False
       For n = 2 To k
           Set data = stab.Cell(i, n).Range
           data.End = data.End - 1
           ttab.Cell(ttab.Rows.Count, n - 1).Range = data
       Next n
   Else
       ttab.Rows.Add
       For n = 2 To k
           Set data = stab.Cell(i, n).Range
           data.End = data.End - 1
           ttab.Cell(ttab.Rows.Count, n - 1).Range = data
       Next n
   End If
Next i

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> thats what I am trying as we speak, but I cannot seem to make that work
> either.  do you have a sample word document i could use as a template to
[quoted text clipped - 49 lines]
>> > Hope this makes sense.  I need help really really bad - Thanks in
>> > advance for your time.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.