[DFIA]

I Have a data base in Excel with the first cell containing the full name of a
person.
I want to customized a word letter starting with "Dear Frank" by using mail
merge and by picking up only the given name (first name) of the full name in
the cell of the data base.
Is there a way to do that?
Thank you.

[Peter Jamieson]

If possible, create an additional column in the Excel data source.

Otherwise, as long as
a. you are using Office 2002 or later and are connecting using the
default method (OLE DB)
b. you have already connected to the data source manually
c. you should be able to use something like the following approach to
return an extra column that contains only the first name.

Suppose you are getting your data from Sheet1 of the workbook, and the
name of the column containing the full name is "fullname" and you do not
already have a column called firstname.

Open the VBA Editor and type the following in the Immediate window:

?ActiveDocument.MailMerge.Datasource.Querystring

and press the Enter key.

You should see something like

SELECT * FROM `Sheet1$`

Create a new Sub as follows:

Sub splitthename()
ActiveDocument.MailMerge.DataSource.QueryString = _
" SELECT left(fullname,instr(fullname & ' ',' ')-1)" & _
" AS [firstname], * FROM `Sheet1$`"
End Sub

In other words, you need to keep the same
FROM `Sheet1$`
as before. If you don't like those backquotes, you can use
FROM [Sheet1$]


Run that sub, then use Edit Recipients to verify that you are seeing a
new column called firstname and that it contains the data you want. If
the Sub does not execute correctly, the connection will probably be lost
and you will either have to recreate it manually before trying again, or
write a sub that does an OpenDataSource.

Once you are getting the data you want, you should be able to save your
document, close it, then re-open it to verify that the query still works.

Peter Jamieson

http://tips.pjmsn.me.uk

On 01/12/2009 15:35, DFIA wrote:
I Have a data base in Excel with the first cell containing the full name of a
person.
I want to customized a word letter starting with "Dear Frank" by using mail
merge and by picking up only the given name (first name) of the full name in
the cell of the data base.
Is there a way to do that?
Thank you.