Printing merged data into 2 columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

magicdds - 17 Mar 2008 00:48 GMT

I Used mail merge to create a list sorted by category as per Article ID
211303. It works just as described. The article has the "City" printed
followed by the "Employees" and "sales" listed in a single column. I want my
results to look as follows:

Atlanta: Smith $3000
Gates $50,000
Henderson $10,000

Houston: Jones $8,000
Kelly $9,000
Peterson $0

How can I program in that after "City" is printed, the cursor needs to tab
over to the right column, prior to printing each of the "Employees" . Also,
if the "Employees" data is so long that it continues over to the next line,
it needs to first tab over to the right side column before continuing to
print.

Thanks for any help you can give.
Mark

Reply to this Message

Doug Robbins - Word MVP - 17 Mar 2008 07:46 GMT

If you create a Catalog (or 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 (City) in the first
cell in the row and then execute that merge to a new document and then run
the following macro with that new document as the active document, it will
create a new document with the data arranged in the way that you want.

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)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(1, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
j = ttab.Rows.Count
For i = 2 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 = False
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).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).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

>I Used mail merge to create a list sorted by category as per Article ID
> 211303. It works just as described. The article has the "City" printed
[quoted text clipped - 20 lines]
> Thanks for any help you can give.
> Mark

Reply to this Message

magicdds - 17 Mar 2008 08:22 GMT

This is similar to the code that I used to merge my Access Data into the Word
Letter:

{ IF { MERGESEQ } = "1" "{ MERGEFIELD City }" "" }<ENTER>
{ SET Place1 { MERGEFIELD City }}<ENTER>
{ If { Place2 } <> { Place1 }"<ENTER>
{ MERGEFIELD City }<ENTER>
<ENTER>
{ MERGEFIELD Employee }{ MERGEFIELD Sales }" "{ MERGEFIELD Employee }{
MERGEFIELD Sales }" }{ SET Place2 { MERGEFIELD City }}<ENTER>

I just need to get { MERGEFIELD Employee }{ MERGEFIELD Sales } to tab over
to a certain column. Isn't there an easier way than to write all the code
below (especially since I'm not familiar with most of it)?

> If you create a Catalog (or in Word XP and later, it's called Directory)
> type mailmerge main document with the mergefields in the cells of a one row
[quoted text clipped - 71 lines]
> > Thanks for any help you can give.
> > Mark

Reply to this Message

Doug Robbins - Word MVP - 17 Mar 2008 10:45 GMT

In your catalog (or directory) mail merge main insert a three column one row
table and in the first row, set up the following field construction:

{ IF { MERGESEQ } = "1" "{ MERGEFIELD City }{ SET Place2 { MERGEFIELD
City} }" "{ SET Place1 { MERGEFIELD City }}" }{ IF { Place2 } <> { Place1 }
"{ MERGEFIELD City }" "" }

In the second column, insert

{ MERGEFIELD Employee }

and in the third column, insert

{ MERGEFIELD Amount }{SET Place2 { MERGEFIELD City} }

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

> This is similar to the code that I used to merge my Access Data into the
> Word
[quoted text clipped - 94 lines]
>> > Thanks for any help you can give.
>> > Mark

Reply to this Message

magicdds - 18 Mar 2008 04:36 GMT

Thanks for the help so far. I almost have what I need.

The problem is that when I merge the data to the word document, while the
data prints properly in each cell, the height of the cell is too tall. It
seems that the cell adds extra carriage returns to the merged document, due
to the length of the conditional statements that are in the cell. How can I
get the document to print with no empty lines between each row of the the
table?

> In your catalog (or directory) mail merge main insert a three column one row
> table and in the first row, set up the following field construction:
[quoted text clipped - 108 lines]
> >> > Thanks for any help you can give.
> >> > Mark

Reply to this Message

Doug Robbins - Word MVP - 18 Mar 2008 10:08 GMT

You must either have some carriage returns in the cells, or it is the
spacing between your paragraphs.

With the mailmerge main document open, press the ¶ button to show the
paragraph marks. There should be none inside the table.

One thing I have noticed that the first time that you run the merge, it says
that there is an error in record 1, I think it is and the name of the citry
is inserted twice. When you run it again however, the error or duplication
of the city name does not occur.

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

> Thanks for the help so far. I almost have what I need.
>
[quoted text clipped - 134 lines]
>> >> > Thanks for any help you can give.
>> >> > Mark

Reply to this Message

magicdds - 19 Mar 2008 09:07 GMT

I am looking at the paragraph marks. While there are none inside the table,
there is one on the left side of the page, just below the table. This is what
is causing the extra blank line between each row of the table because if I
type something on that line, it prints between each row of data in the table.
The problem is, I can't seen to get that paragraph mark to delete. Any
suggestion on how to delete it.

This also presents a new problem. When I type a concluding paragraph to my
letter, under the table, instead of that paragraph printing once, under the
table, it prints after each row of the table. How do I get it to only print
once at the end of the table?

> You must either have some carriage returns in the cells, or it is the
> spacing between your paragraphs.
[quoted text clipped - 145 lines]
> >> >> > Thanks for any help you can give.
> >> >> > Mark

Reply to this Message

Doug Robbins - Word MVP - 19 Mar 2008 09:59 GMT

There will always be one ¶ after the table, but if there is only one of
them, when you execute the merge, everything will be in one single table.
Are you saying that each record is in a table of its own with a blank line
between them?

If you want additional text in the document, you must add that after
executing the merge.

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

>I am looking at the paragraph marks. While there are none inside the table,
> there is one on the left side of the page, just below the table. This is
[quoted text clipped - 179 lines]
>> >> >> > Thanks for any help you can give.
>> >> >> > Mark

Reply to this Message

magicdds - 19 Mar 2008 16:49 GMT

I believe that I am not explaining myself properly so I will show you all the
information, with the problem printout.

1)Here is the data being merged from Access (There are 4 fields of data):

PatientFirstName PatientName Category DxTxInf
------------------------------------------------------------------------------------------------
John    John Doe Diagnosis    Class II, Division1
John    John Doe Arch Length Discrepancy Moderate Maxillary
Crowding
John    John Doe Arch Length Discrepancy Severe Mandibular Crowding
John    John Doe    Crossbite    Bilateral posterior
crossbite

2) Here is the letter prior to merging (the vertical lines below, represent
the dividing line between the two columns to be printed in the table; Column
#1 is the category field, Column #2 is the DxTxInfo field):

{ If { Mergeseq } = "1" "Dear { MERGEFIELD "PatientFirstName" }:

This treatment information is about {MERGEFIELD "pATIENTnAME" }:

" "" }
{ iF { MERGESEQ } = "1" "{MERGEFIELD | {MERGEFIELD "DxTxInfo" }{SET place2
"Category" }:" ""}{SET place1 { | {MERGEFIELD "Category" }}
MERGEFIELD "Category" }}{ IF { place2 |
} <> { place1 } " { MERGEFIELD |
"Category" }: ""} |
We would like to welcome «PatientFirstName» to our office and hope you enjoy
your experience.
Sincerely,
Dr. Jim Jones

3) Here is the result after merging:

Dear John:

This treatment information is about John Doe:

Diagnosis:     Class II, Division1
We would like to welcome John to our office and hope you enjoy your
experience.
Sincerely,
Dr. Jim Jones
Arch Length Discrepancy:     Moderate Maxillary Crowding
We would like to welcome John to our office and hope you enjoy your
experience.
Sincerely,
Dr. Jim Jones
    Severe Mandibular Crowding
We would like to welcome John to our office and hope you enjoy your
experience.
Sincerely,
Dr. Jim Jones
Crossbite:     Bilateral posterior crossbite
We would like to welcome John to our office and hope you enjoy your
experience.
Sincerely,

Dr. Jim Jones

4) This is what I would like it to look like:

Dear John:

This treatment information is about John Doe:

Diagnosis: Class II, Division1
Arch Length Discrepancy:     Moderate Maxillary Crowding
    Severe Mandibular Crowding
Crossbite:     Bilateral posterior crossbite

We would like to welcome John to our office and hope you enjoy your
experience.
Sincerely,

Dr. Jim Jones

___________________________
I appreciate your help so far. If you can tell me what I am doing wrong,
that would be great.

THANKS
Mark

Reply to this Message

Doug Robbins - Word MVP - 19 Mar 2008 20:47 GMT

You are trying to do something that Word's mail merge utility does not have
the ability to do. As your data is in Access, I would use an Access report
to create the letters.

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

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

>I believe that I am not explaining myself properly so I will show you all
>the
[quoted text clipped - 88 lines]
> THANKS
> Mark

Reply to this Message

Rate this thread: