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 / June 2005

Tip: Looking for answers? Try searching our database.

Mailmerge or Pivot Table?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Tosca - 12 Jun 2005 12:58 GMT
Hi everyone

I'm not sure where to post this, so I've started here!

I have a simple table in Excel 2003 and am working with Word 2003.  The
first column of data has a number of countries (which is repeated) and in
the relevant rows, there is information relating to the country (such as
year, region and population at the end of that year).

I need to collate the data to labels - at the centre top of each label the
name of the country and then columns of data.  In this example, the first
column would be "Year" and the second would be "Region" and the third would
be "Population".  I know that I can use mailmerge but I've only been able to
do this and have the country in the first column, year in the second, region
in the third and population in the fourth.  The repeating country name is
not necessary as I need this as a title on the label.  I also know that I
could use a Pivot Table to collate the data but I've only been able to get
it to count (or do other numerical analysis of the data), rather than give
the actual data.

I may be "barking up the wrong tree" with these approaches so I'd be
grateful for thoughts about how this could be done.  Finally, I know that I
could set an AutoFilter to display the data by individual countries but this
wouldn't allow me to print the labels as I'd like (or could it in some
way?).

Thanks for your time and patience!
Doug Robbins - 13 Jun 2005 00:46 GMT
Word does not really have the ability to perform a "multiple items per
condition (=key field)" mailmerge.

See the "Multiple items per condition" item under the "Special merges"
section of fellow MVP Cindy Meister's website at

http://homepage.swissonline.ch/cindymeister/MergFram.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 may 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

> Hi everyone
>
[quoted text clipped - 23 lines]
>
> Thanks for your time and patience!
Tosca - 13 Jun 2005 09:49 GMT
Thank you Doug for the information.  I'll certainly investigate the options
that you've suggested.

I'll post back with my results.

> Word does not really have the ability to perform a "multiple items per
> condition (=key field)" mailmerge.
[quoted text clipped - 54 lines]
>    End If
> Next i
Tosca - 17 Jun 2005 08:56 GMT
Hi Doug (again!)

I managed to tweak the macro and the way that I generated the mail merge
document.  It works a treat now.

Thank you again for your input.  I hope that this will be of help to someone
else who finds themself requiring such capability.

Rate this thread:






 
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.