[Mark]

Hi all,

I have a mail merge question, does anyone of you sage beings know how I can
truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first
initial?

I.e. instead of showing Mark Ryan, showing M Ryan instead?

Many thanks for your help!

[Graham Mayor]

You can't do this with fields. You will have to create another initial field
in your data file. This is easy enough to do in (eg) Excel.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Mark wrote:
Hi all,

I have a mail merge question, does anyone of you sage beings know how
I can truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its
first initial?

I.e. instead of showing Mark Ryan, showing M Ryan instead?

Many thanks for your help!

[Peter Jamieson]

I would go Graham's route, but if you cannot do that, you can probably have
26 IF fields that do e,.g.

{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "A*" "A" ""
}{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "B*" "B" ""
}

and so on...

Peter Jamieson
"Mark" wrote in message
...
Hi all,

I have a mail merge question, does anyone of you sage beings know how I
can
truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first
initial?

I.e. instead of showing Mark Ryan, showing M Ryan instead?

Many thanks for your help!

[fscodave]

That's /exactly/ the type of text manipulation I've been able to do in
WP in its merge language and have failed to figure out how to do in
Word. At least now I know not to waste my time.

I had a routine to reduce the full name of an insurance company to a
short name. (Like /Dominion of Canada/ to just /Dominion/.) And no, I
can't modify the merge field: I just get a .dbf file straight from IT.
I've been trying to figure out some way around Word's limitations. I
posted a message a while ago, and got a somewhat unhelpful answer to
forget what I could do in WP and move on to learn SQL or something.

WP has merge commands like ASSIGN(vTxt;FIELD(Txt)), to assign whatever
to the variable vTxt (in this case, a field), and string commands, so
then you can search for, say, the position of the first space with a
string position command like STRPOS(" ";vTxt) and combine it with a
substring command to subtract the bit you want and assign that to your
variable.

Sigh.

Is there no way to invoke VBA? You know, somehow assign that field with
the name to a variable, then invoke VBA to manipulate it, then insert
the modified variable into the document? Again, I've done that with
fields where I get want the user to be able to modify the info if need
be, but not necessarily. [The routine is: 1. assign field to variable..
2. invoke macro with prompt where variable pre-fills the prompt. 3.
user can modify or not the variable. 4. macro stops, variable is
inserted into text in WP's merge language.]

But so far I haven't found the equivalent of WP's VARIABLE(vTxt)
command to insert the modified-or-not variable into the text...

David Evans

Peter Jamieson wrote:
I would go Graham's route, but if you cannot do that, you can probably have
26 IF fields that do e,.g.

{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "A*" "A" ""
}{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "B*" "B" ""
}

and so on...

Peter Jamieson
"Mark" wrote in message
...
Hi all,

I have a mail merge question, does anyone of you sage beings know how I
can
truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first
initial?

I.e. instead of showing Mark Ryan, showing M Ryan instead?

Many thanks for your help!

[Doug Robbins - Word MVP]

You can use the .dbf file as the datasource for a catalog (or in Word XP and
later it is called directory) type mailmerge, in the main document of which
you insert the mergefields inside the cells of a one row table. When you
execute that merge to a new document, the document that is produced will
contain a table with a row of data for each record in the datasource. You
could then use a macro to iterate through the rows of that table to do
whatever you want with the data.

It it is always the same thing, all of the above could be automated, leaving
you then with a table of data that can be used as the data source for your
original mailmerge.

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

"fscodave" wrote in message
oups.com...
That's /exactly/ the type of text manipulation I've been able to do in
WP in its merge language and have failed to figure out how to do in
Word. At least now I know not to waste my time.

I had a routine to reduce the full name of an insurance company to a
short name. (Like /Dominion of Canada/ to just /Dominion/.) And no, I
can't modify the merge field: I just get a .dbf file straight from IT.
I've been trying to figure out some way around Word's limitations. I
posted a message a while ago, and got a somewhat unhelpful answer to
forget what I could do in WP and move on to learn SQL or something.

WP has merge commands like ASSIGN(vTxt;FIELD(Txt)), to assign whatever
to the variable vTxt (in this case, a field), and string commands, so
then you can search for, say, the position of the first space with a
string position command like STRPOS(" ";vTxt) and combine it with a
substring command to subtract the bit you want and assign that to your
variable.

Sigh.

Is there no way to invoke VBA? You know, somehow assign that field with
the name to a variable, then invoke VBA to manipulate it, then insert
the modified variable into the document? Again, I've done that with
fields where I get want the user to be able to modify the info if need
be, but not necessarily. [The routine is: 1. assign field to variable..
2. invoke macro with prompt where variable pre-fills the prompt. 3.
user can modify or not the variable. 4. macro stops, variable is
inserted into text in WP's merge language.]

But so far I haven't found the equivalent of WP's VARIABLE(vTxt)
command to insert the modified-or-not variable into the text...

David Evans

Peter Jamieson wrote:
I would go Graham's route, but if you cannot do that, you can probably
have
26 IF fields that do e,.g.

{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "A*" "A" ""
}{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "B*" "B" ""
}

and so on...

Peter Jamieson
"Mark" wrote in message
...
Hi all,

I have a mail merge question, does anyone of you sage beings know how I
can
truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first
initial?

I.e. instead of showing Mark Ryan, showing M Ryan instead?

Many thanks for your help!

[Peter Jamieson]

Unfortunately, it's difficult to know how to reply to people who have made
full use of WP's facilities: it arguably always had a better way to do
merges. The basic problems with Word compared with WP (as I understand it
from a brief encounter with WP rather a long time ago!) are that
a. Word Macros are basically tied to Word templates and documents and lack
the independence of the sort of macros you could write in WP
b. Word MailMerge assumes a single tabular data source (i.e. same fields in
each row) whereas you could, in effect, structure a WP data source how you
wanted
c. Word's field language is poor (in fact I think its development was
basically abandoned in favour of effort on VBA, despite the fact that the
two things work in completely different ways.

I had a routine to reduce the full name of an insurance company to a
short name. (Like /Dominion of Canada/ to just /Dominion/.) And no, I
can't modify the merge field: I just get a .dbf file straight from IT.
I've been trying to figure out some way around Word's limitations. I
posted a message a while ago, and got a somewhat unhelpful answer to
forget what I could do in WP and move on to learn SQL or something.

If that message was from me, it would not have been posted without thought.
You simply won't find the facilities you had within WP. The easiest way to
replace /some/ of them is to adapt, and there are various ways to do that.
However, it isn't particularly easy to work around some of Word's
limitations, e.g. trying to use multiple data sources in a merge.

Using SQL to modify your data source is one way around some of the problems.
There are also plenty of fancy tricks you can do with fields, but in the end
there's either a fancy trick that can be used to achieve a particular
effect, or solve a particular problem, or there isn't, in which case you
obviously have to fnd a different approach.

For Field tips n tricks, useful sources are probably material by Cindy
Meister, Doug Robbins, Graham Mayor, Charles Kenyon, macropod and myself
(including the stuff posted on Greg Maxey's site). However, I don't know of
a really comprehensive list of such stuff, nor am I about to produce one in
the next 5 minutes :-)

In my view the only way you are likely to be able to produce macros that
initiate merges that are even remotely similar to the stuff you did with WP
is to learn to use VBA (or VB.NET or whatever) to do the same thing. If you
want to get info. from users, you either use Word ASK/FILLIN fields over
which you will have little control, , or you use VBA-level facilities such
as VBA Userforms which unfortunately require you to dive headlong into VBA,
or simpler facilities such as VBA InputBox facilities.

WP has merge commands like ASSIGN(vTxt;FIELD(Txt)), to assign whatever
to the variable vTxt (in this case, a field), and string commands, so
then you can search for, say, the position of the first space with a
string position command like STRPOS(" ";vTxt) and combine it with a
substring command to subtract the bit you want and assign that to your
variable.

You just don't get this in the Word field system. Ways to achieve similar
things a
a. manipulate the data source
b. use VBA and Word MailMerge Events to get at the data in your data source
before it gets stuffed into the mail merge main document
c. use a DATABASE field to extract info. from a mailmerge field.
Unfortunately, this can only be sed if you can guarantee that your data does
not have characters such as single-quotes in it, and MS has done something
in recent version sof Word that makes this much more unreliable.

Is there no way to invoke VBA? You know, somehow assign that field with
the name to a variable, then invoke VBA to manipulate it, then insert
the modified variable into the document? Again, I've done that with
fields where I get want the user to be able to modify the info if need
be, but not necessarily. [The routine is: 1. assign field to variable..
2. invoke macro with prompt where variable pre-fills the prompt. 3.
user can modify or not the variable. 4. macro stops, variable is
inserted into text in WP's merge language.]

Yes, use Word events. Have a look at Cindy Meister's website or look for
articles in this group (e.g. via groups.google.com). But you can't insert
the modified field using a MERGEFIELD field: you either have to stuff the
modified text into the document or update a Document variable or Document
property which is then inserted during the merge using a { DOCVAROABLE }
field or { DOCPROPERTY } field.

My best shot for now...

Peter Jamieson

"fscodave" wrote in message
oups.com...
That's /exactly/ the type of text manipulation I've been able to do in
WP in its merge language and have failed to figure out how to do in
Word. At least now I know not to waste my time.

I had a routine to reduce the full name of an insurance company to a
short name. (Like /Dominion of Canada/ to just /Dominion/.) And no, I
can't modify the merge field: I just get a .dbf file straight from IT.
I've been trying to figure out some way around Word's limitations. I
posted a message a while ago, and got a somewhat unhelpful answer to
forget what I could do in WP and move on to learn SQL or something.

WP has merge commands like ASSIGN(vTxt;FIELD(Txt)), to assign whatever
to the variable vTxt (in this case, a field), and string commands, so
then you can search for, say, the position of the first space with a
string position command like STRPOS(" ";vTxt) and combine it with a
substring command to subtract the bit you want and assign that to your
variable.

Sigh.

Is there no way to invoke VBA? You know, somehow assign that field with
the name to a variable, then invoke VBA to manipulate it, then insert
the modified variable into the document? Again, I've done that with
fields where I get want the user to be able to modify the info if need
be, but not necessarily. [The routine is: 1. assign field to variable..
2. invoke macro with prompt where variable pre-fills the prompt. 3.
user can modify or not the variable. 4. macro stops, variable is
inserted into text in WP's merge language.]

But so far I haven't found the equivalent of WP's VARIABLE(vTxt)
command to insert the modified-or-not variable into the text...

David Evans

Peter Jamieson wrote:
I would go Graham's route, but if you cannot do that, you can probably
have
26 IF fields that do e,.g.

{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "A*" "A" ""
}{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "B*" "B" ""
}

and so on...

Peter Jamieson
"Mark" wrote in message
...
Hi all,

I have a mail merge question, does anyone of you sage beings know how I
can
truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first
initial?

I.e. instead of showing Mark Ryan, showing M Ryan instead?

Many thanks for your help!

[fscodave]

Peter Jamieson wrote:

If that message was from me, it would not have been posted without thought.


Sorry. I'm obviously still grumpy about this change being forced on us
from a sturdy system I designed 6 or 7 years ago.

David

[Peter Jamieson]

For he sake of the other participants, I suggest we don't start a grumpiness
contest - we seem too evenly matched :-)

Peter Jamieson
"fscodave" wrote in message
ps.com...

Peter Jamieson wrote:

If that message was from me, it would not have been posted without
thought.


Sorry. I'm obviously still grumpy about this change being forced on us
from a sturdy system I designed 6 or 7 years ago.

David

[fscodave]

I am a complete tyro, but I did get an answer in a new message I
posted. This was the answer from Cindy Meister:

n the mail merge FAQ on my website you'll find a section on Word
2002/2003, and in there a discussion about using the mail merge events.
There's a sample file that demonstrates basically what you need to do
(manipulate the value of the merge field and put it in the document)

http://homepage.swissonline.ch/cindy...002/MM2002.htm

Cindy Meister

[end of quote]

Basically, you download a zip file after clicking on "mail merge
events" from the page she points you to. The zip file has two files,
both of which have to be unzipped to the same folder. MergeEvents.Doc
is the main document, with some macros inside of it. I don't understand
most of the code, which is in VBA, but I've figured out this much: the
merge points to the other document in the zip file, employees.doc,
which has the field FirstName. The guts of the relevant macro is a
function GetInitial, and the most relevant line is this:

szIntial = Left(doc.MailMerge.DataSource.DataFields("FirstNam e").Value,
1) & "."

[typo with the "intial" instead of "initial" is there, but doesn't
affect the function]

Earlier in the macro the datasource had been defined, I guess, and you
can see that it refers to the "FirstName" field, and takes the leftmost
element of that field and adds a period to it.

However, when I run the merge, I only get the first two records to
merge in the resulting new document. Plus, I don't understand most of
the rest of the macro. I gather that the initial is then put into the
relevant bookmark. So it's possible to do what you asked, I think, but
we'll have to brush up on the VBA to make it work!

David


Mark wrote:
Hi all,

I have a mail merge question, does anyone of you sage beings know how I can
truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first
initial?

I.e. instead of showing Mark Ryan, showing M Ryan instead?

Many thanks for your help!

[Peter Jamieson]

In another message I alluded to the fact that you can sometimes use DATABASE
fields to do this kind of stuff. It's kludgy and nasty, but it may work.

First, create a blank file (e.g. using Notepad) called t.udl. Let's say it's
in a folder called c:\a.

Then insert the following DATABASE field

{ DATABASE \d "C:\\a\\t.udl" \c "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\a\\;Jet OLEDB:Engine Type=96;" \s "SELECT left('{ MERGEFIELD
FirstName }',1)" }

Make sure all the {} are the special field code braces you can insert using
ctrl-F9.

Don't use the \h switch (which adds a header) to the field. When the
resulting table only contains one row/column Word does not wrap itin a
table.

Unfortunately, Word seems now to add a paragraph mark before the result
sometimes. It doesn't seem to do this all the time, but it does seem to
start doing it after a while for reasons I haven't been able to fathom. Also
a. it will fail if the Firstname field contains a "'" character, and
perhaps other awkward characters
b. you may find one of Word's irritating security messages popping up.

Once you have created the t.udl (which is just a "Data Link" file to fool
Word into using its Jet SQL engine to do the SELECT) you shouldn't have to
change any of the text in the DATABASE field except the SELECT statement.
Because this uses the Jet dialect of SQL, you'll find that many, if not most
of the functions available in Word VBA can also be applied to the operand -
e.g. left, mid, right, instr, etc. Some things may have slightly different
parameters than the functions available in Word, and some, such as replace,
fail for no apparent reason.

Peter Jamieson

"fscodave" wrote in message
oups.com...
I am a complete tyro, but I did get an answer in a new message I
posted. This was the answer from Cindy Meister:

n the mail merge FAQ on my website you'll find a section on Word
2002/2003, and in there a discussion about using the mail merge events.
There's a sample file that demonstrates basically what you need to do
(manipulate the value of the merge field and put it in the document)

http://homepage.swissonline.ch/cindy...002/MM2002.htm

Cindy Meister

[end of quote]

Basically, you download a zip file after clicking on "mail merge
events" from the page she points you to. The zip file has two files,
both of which have to be unzipped to the same folder. MergeEvents.Doc
is the main document, with some macros inside of it. I don't understand
most of the code, which is in VBA, but I've figured out this much: the
merge points to the other document in the zip file, employees.doc,
which has the field FirstName. The guts of the relevant macro is a
function GetInitial, and the most relevant line is this:

szIntial = Left(doc.MailMerge.DataSource.DataFields("FirstNam e").Value,
1) & "."

[typo with the "intial" instead of "initial" is there, but doesn't
affect the function]

Earlier in the macro the datasource had been defined, I guess, and you
can see that it refers to the "FirstName" field, and takes the leftmost
element of that field and adds a period to it.

However, when I run the merge, I only get the first two records to
merge in the resulting new document. Plus, I don't understand most of
the rest of the macro. I gather that the initial is then put into the
relevant bookmark. So it's possible to do what you asked, I think, but
we'll have to brush up on the VBA to make it work!

David


Mark wrote:
Hi all,

I have a mail merge question, does anyone of you sage beings know how I
can
truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first
initial?

I.e. instead of showing Mark Ryan, showing M Ryan instead?

Many thanks for your help!