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 / May 2004

Tip: Looking for answers? Try searching our database.

Alphabetical Index

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Michael Koerner - 02 May 2004 18:37 GMT
I have a Name and Address merge in Word 2000 from an Excel Spreadsheet. Would
like to know when the merge <<LastName>> say jumps from A to B, if a new row
could be inserted into the Word Table indicating the change., to look something
like this:

A
Adams, John
B
Brown, Mary

Signature

Regards
Michael Koerner

Doug Robbins - Word MVP - 03 May 2004 00:02 GMT
Sort the datasource on the Last Name then First Name columns then add
another column to your Excel datasource that you populate by using a formula
that grabs the first letter of the LastName field (=Left(cellref, 1).  Then
set up a director or catalog type mailmerge main document with the
mergefields in the first row of a one row table so that it produces

A    Adams, John    [other fields]
A    [additional names beginning with A
B    Brown, Mary
B    [additional names beginning with B
etc.

Then run the following macro of the document created by executing that
merge:

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.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
   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

It will create a new documnet with the data arranged as you want it.

Signature

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested.  Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

> I have a Name and Address merge in Word 2000 from an Excel Spreadsheet. Would
> like to know when the merge <<LastName>> say jumps from A to B, if a new row
[quoted text clipped - 5 lines]
> B
> Brown, Mary
Michael Koerner - 03 May 2004 00:58 GMT
Thanks Doug. Will give it a shot.

Signature

Regards
Michael Koerner

Sort the datasource on the Last Name then First Name columns then add
another column to your Excel datasource that you populate by using a formula
that grabs the first letter of the LastName field (=Left(cellref, 1).  Then
set up a director or catalog type mailmerge main document with the
mergefields in the first row of a one row table so that it produces

A    Adams, John    [other fields]
A    [additional names beginning with A
B    Brown, Mary
B    [additional names beginning with B
etc.

Then run the following macro of the document created by executing that
merge:

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.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
   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

It will create a new documnet with the data arranged as you want it.

Signature

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested.  Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

> I have a Name and Address merge in Word 2000 from an Excel Spreadsheet.
Would
> like to know when the merge <<LastName>> say jumps from A to B, if a new
row
> could be inserted into the Word Table indicating the change., to look
something
> like this:
>
> A
> Adams, John
> B
> Brown, Mary
 
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.