Selecting the correct one of two fields to merge from Excel to Word document

[bobsthrowaway]

Using WORD 2004, I'm trying to build a letter where the salutation can
be "Dear John" if I know John, or "Dear Mr. Smith" if I do not. I have
the two data fields set up in Excel. All of the LAST NAME fields have
data. Only the SALUTATION fields where I know the person have data.
I've been trying to build a conditional IF statement that will make
this work but have been failing miserably.

Can anyone suggest a method to print the salutation if there is one or
to print the last name if there is no salutation that avoids the issue
of printing BOTH the salutation and the last name?

I've been using Data Merge Manager to the extent that it is appropriate
and then going on from there manually building «IF» statements with
Data Merge Manager fields embedded in them.

The issues: Sometimes the commands print. At all times I get double
results, BOTH salutations and last names in those cases where there are
saluations. It prints "Dear Jerry Mr. Smith" along with pieces of my
commands.

Thank you.

 

[Elliott Roper]

Using WORD 2004, I'm trying to build a letter where the salutation can
be "Dear John" if I know John, or "Dear Mr. Smith" if I do not. I have
the two data fields set up in Excel. All of the LAST NAME fields have
data. Only the SALUTATION fields where I know the person have data.
I've been trying to build a conditional IF statement that will make
this work but have been failing miserably.

Can anyone suggest a method to print the salutation if there is one or
to print the last name if there is no salutation that avoids the issue
of printing BOTH the salutation and the last name?

I've been using Data Merge Manager to the extent that it is appropriate
and then going on from there manually building «IF» statements with
Data Merge Manager fields embedded in them.

The issues: Sometimes the commands print. At all times I get double
results, BOTH salutations and last names in those cases where there are
saluations. It prints "Dear Jerry Mr. Smith" along with pieces of my
commands.

It can get tricky. You need to carefully manage a viper's nest of IF
statements. Here is mine. You are welcome to it.
It is slightly more complicated than yours, my spreadsheet has another
column called nice, which has a "y" in it if we are on first name
terms, and the title is in its own column. Still, it might be
encouragement that you are on the right track.

Dear { IF { MERGEFIELD surname } <> "" "} IF { MERGEFIELD nice}=
"y" "{MERGEFIELD first_name }" "{ MERGEFIELD title } {
MERGEFIELD surname}" }" "Sir/Madam" },
Insert body of letter here.
Yours sincerely,
Elliott (who hopes this is copied correctly)

all of the " characters are highly significant, as are some of the
spaces but I can't remember which ones after all these years.

of course the { } constructions are not your common or garden curly
braces. They are Microsoft's evil twin curly braces aka fields. I hit
F9 to bring them on, but I might have changed it to that from the
default.

Read the help on mergefield. It is not pretty.

If you think that is hairy, you should see how I only print the
significant lines in addresses. Actually you shouldn't.

 

[Peter Jamieson]

(who hopes this is copied correctly)

There's just one typo where you have reversed one of the field braces, i.e.
it should be

Dear { IF { MERGEFIELD surname } <> "" "{ IF { MERGEFIELD nice}=
"y" "{MERGEFIELD first_name }" "{ MERGEFIELD title } {
MERGEFIELD surname}" }" "Sir/Madam" },
Insert body of letter here.
Yours sincerely,
Elliott (who hopes this is copied correctly)

of course the { } constructions are not your common or garden curly
braces. They are Microsoft's evil twin curly braces aka fields. I hit
F9 to bring them on, but I might have changed it to that from the
default.

"Command + F9" should do it.

Peter Jamieson

 

[Elliott Roper]

There's just one typo where you have reversed one of the field braces, i.e.
it should be

Dear { IF { MERGEFIELD surname } <> "" "{ IF { MERGEFIELD nice}=
"y" "{MERGEFIELD first_name }" "{ MERGEFIELD title } {
MERGEFIELD surname}" }" "Sir/Madam" },
Insert body of letter here.
Yours sincerely,
Elliott (who hopes this is copied correctly)

Thanks Peter. You are right.

I wish there were a way of cutting field expressions from Word and
pasting them into a news client. I did that one by doing a screen grab
and typing from that. I should have made it bigger before grabbing. I
did consider OCR-ing the grab. Next time, I'll stick the grab on my web
site.

The whole process is *so* 1960. Miss Roper, hop up on my knee and take
a letter!

 

[Peter Jamieson]

Yes, it's a pain.

Cindy Meister has some VBA that can help at

http://homepage.swissonline.ch/cindymeister/mergfaq.htm

I haven't tried it on Mac.

Peter Jamieson

 

[Elliott Roper]

Yes, it's a pain.

Cindy Meister has some VBA that can help at

http://homepage.swissonline.ch/cindymeister/mergfaq.htm

I haven't tried it on Mac.

Now that is an interesting article.
I tried the Macro, It almost works. It builds the string almost OK but
the clipboard ends up with garbage. I guess the PutInClipboard method
is not working the same on the Mac. Nor is it substituting the "{" and
"}" in place of Chr(19) and Chr(21).

I'll need to hone my VBA debugging skills. Like find out why
Debug.Print MyData barfs but Debug.Print NewString is perfectly happy.

It would be a useful tool for anyone discussing fields. I'll post it up
here when I get it working.

 

[Peter Jamieson]

I think the problem with both the "{" and the Clipboard is the lack of
Unicode support in VBA on Mac. VBA seems to get very confused. It's
difficult to see how other people successfully manipulate strings extracted
from Word without having cracked this problem though...

Peter Jamieson

 

[Elliott Roper]

I think the problem with both the "{" and the Clipboard is the lack of
Unicode support in VBA on Mac. VBA seems to get very confused. It's
difficult to see how other people successfully manipulate strings extracted
from Word without having cracked this problem though...

<snip>

Yep, I was thinking it might be that. I will try a few more things
before I give up. It is collecting the characters. It should not be too
hard to discover what it thinks the braces are really.

I can always fall back to placing the text back in the document as
Cindy's other macro does if I can't get the clipboard to play nicely.

 

[Peter Jamieson]

I can only get the thing to work by using a byte array. When you try to put
it in the clipboard something clearly thinks it is twice as long as it
actually is, so you get a load of rubbish at the end. However, it seems to
be possible to stuff the byte array back into a range so the following
nearly works but needs the document-related stuff at the end to be improved
IMO. I haven't tested with a range of international characters etc.

Sub FieldCodeToStringInNewDoc()
' Converted from Cindy Meister macro by Peter Jamieson 2006-08-10
Dim bCurrSetting As Boolean
Dim bytField() As Byte
Dim l As Long
Dim lChar As Long
Dim objDisplay As Word.View
Dim objDocument As Word.Document
Set objDisplay = ActiveWindow.View
bCurrSetting = objDisplay.ShowFieldCodes
If Not bCurrSetting Then objDisplay.ShowFieldCodes = True
bytField = Selection.Range.Text
' Assumes all the characters are double-byte
For l = LBound(bytField) To UBound(bytField) - 1 Step 2
lChar = (256 * bytField(x)) + bytField(x + 1)
Select Case lChar
Case 19
bytField(x + 1) = 123
Case 21
bytField(x + 1) = 125
Case Else
End Select
Next
objDisplay.ShowFieldCodes = bCurrSetting
Set objDisplay = Nothing
Set objDocument = New Word.Document
objDocument.Content.Text = bytField
' Needs improvement...
objDocument.SaveAs "fieldtext.doc"
End Sub

(Copied by hand so please point out any typos).

I could have sworn I'd been through all that before, but I guess it must
have been a long time ago...

Peter Jamieson