Word mail merge code help

[louis vilan]

I would like to get a little code help on a project. I am only now
starting to learn VB, and to this point, I have tried VB on a limited
basis in Access.

Here's what I am working on that prompted me to ask for your help. I
have a Word 2003 mail merge main document that has about 40 mail merge
fields on it, so you can imagine the size of the Word data source
table. On the main document, I have a table laid out that I would like
to format during the merge, and the format would be conditional and
based on the value merged into onje of the fields. Basically, what I am
trying to do is to format the table as a bar chart; if the value of the
merged field is "1", one cell of the table changes color; if the value
of the merge is "2" then another cell of the table changes color; the
value of the field can also be "3" or "4" and the format would again
change on the value. I have about 100 records in my data table;
actually, it's an ascii txt file with 40 fields and 100 records I
export from Access.

I know I can control the color of the table cells because I have
recorded a couple macros that will automatically format the cells the
way I want them.Unfortunately, Word VB seems like the cruel step child
of VBA I am starting to learn in Access.

In plain english, here are the steps I want to do:
1. During the merge, when mergeField1 on the main doc receives a value
of "1" from the data source, the VB code should run Macro1. Macro1 is a
macro I recorded that will reformat a table cell on the main doc. The
macro knows where the cell is because there is a bookmark in the cell.
If the value is "2", the code should call Macro2, which already has
been recorded to reformat 2 cells, and so on.

2. After reformatting the cell, the merge should continue with the rest
of the fields on the main doc.

I can see the code for the reformatting macros I recorded, so if I can
get help on the code that can determine which macro to call, I'll be in
good shape. I am trying to figure out the Word VB events, so if you can
help me on the code, I'd also like to know wgat event to use to fire
the code.

Thanks to anyone who can help!

 

[macropod]

Hi Louis,

How about a non-vba solution, just using your MERGEFIELDs?

If you embed your MERGEFIELD inside another field, coded, thus:
{IF{SEQ Val}>{MERGEFIELD Value} "" "?"}
you'll get the "?" character if the SEQ field's value is equal to or less
than your MERGEFIELD's value. If you place a series of these one after the
other, you can soon build up a bar chart where you'll get:
'?' for '1',
'??' for '2'
'?????' for '5'
and so on. You don't even need a table if you want a horizontal bar chart.
If you want a vertical bar chart, either place the fields inside a table
column that is only wide enough for 1 character, or insert linefeeds between
them.

For colour, simply format the '?' character to whatever colour you want.

To control the overall size of the bar, simply format the '?' character to
whatever size you want.

If you don't like the aspect ratio, you could use multiple '?' characters in
the MERGEFIELD:
{IF{SEQ Val}>{MERGEFIELD Value} "" "??"}
or a different character. There's lot of other shapes/symbols you could use
for your bars under Insert|Symbol.

Cheers

 

[Peter Jamieson]

What you need to know is that there is no mechanism whereby Word will
execute a specified macro when it updates a specific field during a merge.
(Word can execute macros when you click a MACROBUTTON field, when you
enter/exit a field in an online form, and when you raise an event in a child
control in a document, but none of those is likely to help you during a
merge).

What you probably need is Word's MailMerge events, specifically the
MailMergeBeforeRecordMerge event, which lets you examine the values of the
data source record that's about to be merged and modify the mail merge main
document before Word merges it.

Word VBA Help has an example of ccoding an Application event that should be
enough to get you started.

NB, a major "gotcha" in the Mail merge events is that the
MailMergeBeforeMerge event only fires if you initiate the merge from the
MailMerge task pane.

Peter Jamieson

 

[louis vilan]

Thnaks for the fast help. Both ideas seem good. I like the SEQ isea,
but I'm not really clear on how to do it.
- How do you embed a mergefield in another field?
-Do you have to set the SEQ value?
- If I want to learn more and become self-sufficient at using the SEQ
idea, what should I read...is there a good site or book or reference?
These are probably naive questions, but it was only a couple days ago
that I even realized that Word could be programmed.

Thanks again!

 

[macropod]

Hi Louis,

To embed the MERGEFIELD inside another field:
1. Select the field and press Shift-F9. You should see something like
{MERGEFIELD Value}
2. With the MERGEFIELD still selected, press Ctrl-F9 to embed it inside
another field. You now should see:
{ {MERGEFIELD Value} }
3. Select the space between the two '{ {' and press Ctrl-F9 again to insert
yet another field:
{{ }{MERGEFIELD Value} }
4. Now fill in the spaces between the field braces, replacing the "?" with
your preferred symbol, so that you end up with:
{IF{SEQ Val}>{MERGEFIELD Value} "" "?"}
5. Press F9 to update the field
6. Run you mail merge.

To cater for multiple bars in the chart, you'll need to have a separate SEQ
field, or at least reset it to 1, for each bar in the chart. Either is easy.
To reset, simply change the SEQ field's coding for the 1st entry in each bar
to:
{SEQ Val \ r 1}

There are more details on field usage in the Word help file. You might also
like to check out my Word Field Maths 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=365442
and my Date Calc 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902

Cheers
Note: step 1 isn't really necessary. You could just as easily leave it
looking like «Value».

 

[louis vilan]

Hi Macropod.

Thanx for the help. I think I have it working almost like I want it.
Instead of "x", I inserted right into the code a table cell with the
format I wanted.

One more question: I'd like to place the tablecell/bar at a specific
point on the document. I have about 40 mergefields, and the table
cell/bar mergefield is about the 30th field. I know I can set a
bookmark in the main document, but what command would I use to go to
the bookmark and insert the table cell/bar exactly where I want it ?

Once again, THANX!!!!!!!

 

[macropod]

Hi Louis,

Here's some basic code for updating a bookmark:

Sub UpdateBookmark(BmkNm As String, NewTxt As String)
Dim BmkRng As Range
If Documents.Count > 0 Then
If ActiveDocument.Bookmarks.Exists(BmkNm) Then
Set BmkRng = ActiveDocument.Bookmarks(BmkNm).Range
BmkRng.Text = NewTxt
ActiveDocument.Bookmarks.Add BmkNm, BmkRng
End If
End If
Set BmkRng = Nothing
End Sub

However, since you seem to know where the tablecell/bar has to go, I don't
understand why you need vba for this. Simply inserting the field coding at
that point in the mailmerge template would seem simpler.

Cheers