Can't set display text for hyperlink created in merged Word doc fromExcel data source

Hi:

Merging fields from an Excel 2003 data source to Word 2003 document,
you can create active links (hyperlinks) into the merged document, but
evidently can't set the display text of the link -- the link displays
as itself. This is especially ugly if the path is a network mapped
drive.

Thus, with the ugly URL below, there's no evident way to dress it up
with 'text to display' separate from the URL itself, as far as I've
been able to determine.

If anybody has found a way to do this, it would be very helpful.

-- Roy Zider

Source URL (SrcURL field):
G:\\ABC Email\\Exported\\JLB 2008-06-24\\Chron - extracted\\ABC
Estate_ Contact information.eml

Field codes in merge template:
{HYPERLINK "{MERGEFIELD "SrcURL"}"\@ MERGEFORMAT }

Active link in merged document:
\\k7n\g\ABC Email\Exported\JLB 2008-06-24\Chron - extracted\ABC
Estate_ Contact information.eml

Also annoying is the fact that even if the Excel source document has
active hyperlinks in a field, the merge of this field will transfer
only as text, not as an active hyperlink. So a separate field in
Excel has to be created with the path and file name with double \\ to
generate the \ separators, and use that as the source rather than the
hyperlink itself.

References:
http://homepage.swissonline.ch/cindy...r/MergFram.htm
http://www.gmayor.com/formatting_word_fields.htm

The only way I know is to use VBA to modify the display text, either during
the merge or as a post-merge operation.

I only have the following code for doing it during the merge, and I haven't
tested this code for some time. To see it working

1. Create a new document, connect it to your data source, and insert one
merge field and a bookmark named "mybm"

2. Open up the VBA Editor and
a. insert a class module.
b. name it EventClassModule in the properties box
c. Copy the following code into the module:

Public WithEvents App As Word.Application

Private Sub App_MailMergeBeforeRecordMerge(BYVal Doc As Document, Cancel As
Boolean)
Dim dt as String
Dim lt as String
Dim h as Hyperlink
Dim r as Range

' set the range variable to our placeholder bookmark
Set r = Doc.Bookmarks("mybm").Range

' delete any existing text (this is needed for records after record 1)
r.Text = ""

' construct the link text that you want. I'm assuming your data source
' has fields called idfield and namefield.
lt = http://www.testsite.com?id= & _
Doc.MailMerge.DataSource.DataFields("idfield") & _
"&name=" & _
Doc.MailMerge.DataSource.DataFields("namefield")
' set up the display text that you want. If it should be the same
' as the link text, do that:
dt = lt

' insert the hyperlink you want
Set h = Doc.Hyperlinks.Add(Anchor:=r, Address=lt, TextToDisplay:=dt)

' Set mybm to "cover" the inserted link so it is easy to delete the old
hyperlink

Doc.Bookmarks.Add Name:="mybm", Range:=h.Range

Set r = Nothing
Set h = Nothing

End Sub

3. Insert an ordinary module (the name does not matter) and insert the
following code:

Dim x As New EventClassModule

Sub autoopen()
Set x.App = Word.Application
End Sub

4. Save and close the document. Open it to trigger the autoopen, then
perform a test merge.

NB, if you start changing the code you may find that you need to re-run your
autoopen code again, and/or save/close/open the document.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"FUBARinSFO" wrote in message
...
Hi:

Merging fields from an Excel 2003 data source to Word 2003 document,
you can create active links (hyperlinks) into the merged document, but
evidently can't set the display text of the link -- the link displays
as itself. This is especially ugly if the path is a network mapped
drive.

Thus, with the ugly URL below, there's no evident way to dress it up
with 'text to display' separate from the URL itself, as far as I've
been able to determine.

If anybody has found a way to do this, it would be very helpful.

-- Roy Zider

Source URL (SrcURL field):
G:\\ABC Email\\Exported\\JLB 2008-06-24\\Chron - extracted\\ABC
Estate_ Contact information.eml

Field codes in merge template:
{HYPERLINK "{MERGEFIELD "SrcURL"}"\@ MERGEFORMAT }

Active link in merged document:
\\k7n\g\ABC Email\Exported\JLB 2008-06-24\Chron - extracted\ABC
Estate_ Contact information.eml

Also annoying is the fact that even if the Excel source document has
active hyperlinks in a field, the merge of this field will transfer
only as text, not as an active hyperlink. So a separate field in
Excel has to be created with the path and file name with double \\ to
generate the \ separators, and use that as the source rather than the
hyperlink itself.

References:
http://homepage.swissonline.ch/cindy...r/MergFram.htm
http://www.gmayor.com/formatting_word_fields.htm

Also, Jay Freedman posted this link to some simpler VBA..

http://groups.google.com/group/micro....public.word.*

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Peter Jamieson" wrote in message
...
The only way I know is to use VBA to modify the display text, either
during the merge or as a post-merge operation.

I only have the following code for doing it during the merge, and I
haven't tested this code for some time. To see it working

1. Create a new document, connect it to your data source, and insert one
merge field and a bookmark named "mybm"

2. Open up the VBA Editor and
a. insert a class module.
b. name it EventClassModule in the properties box
c. Copy the following code into the module:

Public WithEvents App As Word.Application

Private Sub App_MailMergeBeforeRecordMerge(BYVal Doc As Document, Cancel
As
Boolean)
Dim dt as String
Dim lt as String
Dim h as Hyperlink
Dim r as Range

' set the range variable to our placeholder bookmark
Set r = Doc.Bookmarks("mybm").Range

' delete any existing text (this is needed for records after record 1)
r.Text = ""

' construct the link text that you want. I'm assuming your data source
' has fields called idfield and namefield.
lt = http://www.testsite.com?id= & _
Doc.MailMerge.DataSource.DataFields("idfield") & _
"&name=" & _
Doc.MailMerge.DataSource.DataFields("namefield")
' set up the display text that you want. If it should be the same
' as the link text, do that:
dt = lt

' insert the hyperlink you want
Set h = Doc.Hyperlinks.Add(Anchor:=r, Address=lt, TextToDisplay:=dt)

' Set mybm to "cover" the inserted link so it is easy to delete the old
hyperlink

Doc.Bookmarks.Add Name:="mybm", Range:=h.Range

Set r = Nothing
Set h = Nothing

End Sub

3. Insert an ordinary module (the name does not matter) and insert the
following code:

Dim x As New EventClassModule

Sub autoopen()
Set x.App = Word.Application
End Sub

4. Save and close the document. Open it to trigger the autoopen, then
perform a test merge.

NB, if you start changing the code you may find that you need to re-run
your
autoopen code again, and/or save/close/open the document.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"FUBARinSFO" wrote in message
...
Hi:

Merging fields from an Excel 2003 data source to Word 2003 document,
you can create active links (hyperlinks) into the merged document, but
evidently can't set the display text of the link -- the link displays
as itself. This is especially ugly if the path is a network mapped
drive.

Thus, with the ugly URL below, there's no evident way to dress it up
with 'text to display' separate from the URL itself, as far as I've
been able to determine.

If anybody has found a way to do this, it would be very helpful.

-- Roy Zider

Source URL (SrcURL field):
G:\\ABC Email\\Exported\\JLB 2008-06-24\\Chron - extracted\\ABC
Estate_ Contact information.eml

Field codes in merge template:
{HYPERLINK "{MERGEFIELD "SrcURL"}"\@ MERGEFORMAT }

Active link in merged document:
\\k7n\g\ABC Email\Exported\JLB 2008-06-24\Chron - extracted\ABC
Estate_ Contact information.eml

Also annoying is the fact that even if the Excel source document has
active hyperlinks in a field, the merge of this field will transfer
only as text, not as an active hyperlink. So a separate field in
Excel has to be created with the path and file name with double \\ to
generate the \ separators, and use that as the source rather than the
hyperlink itself.

References:
http://homepage.swissonline.ch/cindy...r/MergFram.htm
http://www.gmayor.com/formatting_word_fields.htm

Thanks for that...but it is a bit above me and want to ask a simple question:

I am trying to send an Outlook 03 email using Word 03 to merge info from an
Excel 03 datasource which includes a couple of URL fields and have it say
"Click Here" (hiding the full URL using a href=" URL" Click Here/A )

But it doesn't hide the URL and make "Click Here" hot....

Any Ideas?

"Peter Jamieson" wrote:

Also, Jay Freedman posted this link to some simpler VBA..

http://groups.google.com/group/micro....public.word.*

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Peter Jamieson" wrote in message
...
The only way I know is to use VBA to modify the display text, either
during the merge or as a post-merge operation.

I only have the following code for doing it during the merge, and I
haven't tested this code for some time. To see it working

1. Create a new document, connect it to your data source, and insert one
merge field and a bookmark named "mybm"

2. Open up the VBA Editor and
a. insert a class module.
b. name it EventClassModule in the properties box
c. Copy the following code into the module:

Public WithEvents App As Word.Application

Private Sub App_MailMergeBeforeRecordMerge(BYVal Doc As Document, Cancel
As
Boolean)
Dim dt as String
Dim lt as String
Dim h as Hyperlink
Dim r as Range

' set the range variable to our placeholder bookmark
Set r = Doc.Bookmarks("mybm").Range

' delete any existing text (this is needed for records after record 1)
r.Text = ""

' construct the link text that you want. I'm assuming your data source
' has fields called idfield and namefield.
lt = http://www.testsite.com?id= & _
Doc.MailMerge.DataSource.DataFields("idfield") & _
"&name=" & _
Doc.MailMerge.DataSource.DataFields("namefield")
' set up the display text that you want. If it should be the same
' as the link text, do that:
dt = lt

' insert the hyperlink you want
Set h = Doc.Hyperlinks.Add(Anchor:=r, Address=lt, TextToDisplay:=dt)

' Set mybm to "cover" the inserted link so it is easy to delete the old
hyperlink

Doc.Bookmarks.Add Name:="mybm", Range:=h.Range

Set r = Nothing
Set h = Nothing

End Sub

3. Insert an ordinary module (the name does not matter) and insert the
following code:

Dim x As New EventClassModule

Sub autoopen()
Set x.App = Word.Application
End Sub

4. Save and close the document. Open it to trigger the autoopen, then
perform a test merge.

NB, if you start changing the code you may find that you need to re-run
your
autoopen code again, and/or save/close/open the document.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"FUBARinSFO" wrote in message
...
Hi:

Merging fields from an Excel 2003 data source to Word 2003 document,
you can create active links (hyperlinks) into the merged document, but
evidently can't set the display text of the link -- the link displays
as itself. This is especially ugly if the path is a network mapped
drive.

Thus, with the ugly URL below, there's no evident way to dress it up
with 'text to display' separate from the URL itself, as far as I've
been able to determine.

If anybody has found a way to do this, it would be very helpful.

-- Roy Zider

Source URL (SrcURL field):
G:\\ABC Email\\Exported\\JLB 2008-06-24\\Chron - extracted\\ABC
Estate_ Contact information.eml

Field codes in merge template:
{HYPERLINK "{MERGEFIELD "SrcURL"}"\@ MERGEFORMAT }

Active link in merged document:
\\k7n\g\ABC Email\Exported\JLB 2008-06-24\Chron - extracted\ABC
Estate_ Contact information.eml

Also annoying is the fact that even if the Excel source document has
active hyperlinks in a field, the merge of this field will transfer
only as text, not as an active hyperlink. So a separate field in
Excel has to be created with the path and file name with double \\ to
generate the \ separators, and use that as the source rather than the
hyperlink itself.

References:
http://homepage.swissonline.ch/cindy...r/MergFram.htm
http://www.gmayor.com/formatting_word_fields.htm

Peter:

Working on your code now. Had hoped to be able to avoid this, but I
guess not.

-- Roy

' construct the link text that you want. I'm assuming your data
source
' has fields called idfield and namefield.
lt = http://www.testsite.com?id= & _
Doc.MailMerge.DataSource.DataFields("idfield") & _
"&name=" & _
Doc.MailMerge.DataSource.DataFields("namefield")

Peter:

I'm linking to a local data source, not a remote URL. I'm testing
some code now, but don't have the magic strings to link to
"srcExcelData.xls".

-- Roy

Ignore prior comment -- got link to local folder OK now.

Peter:

OK, got it now. Some confusion on my part about the instructions, but
"ask the computer" came through with the answer, as usual.

Thanks again.

-- Roy Zider

Public WithEvents App As Word.Application

Private Sub App_MailMergeBeforeRecordMerge(ByVal Doc As Document,
Cancel As Boolean)
Dim dt As String
Dim lt As String
Dim h As Hyperlink
Dim r As Range


' set the range variable to our placeholder bookmark
Set r = Doc.Bookmarks("mybm").Range

' delete any existing text (this is needed for records after record 1)
r.Text = ""

' construct the link text that you want. I'm assuming your data source
' has fields called idfield and namefield.
' lt = "http://www.testsite.com?id=" & _
' Doc.MailMerge.DataSource.DataFields("idfield") & _
' "&name=" & _
' Doc.MailMerge.DataSource.DataFields("namefield")

' my hyperlink is in the local Excel file in field SrcLink2, as the
link text string
lt = Doc.MailMerge.DataSource.DataFields("SrcLink2")

' set up the display text that you want. If it should be the same
' as the link text, do that:
' dt = lt
' my display text is in field SrcSJ (source subject)
dt = Doc.MailMerge.DataSource.DataFields("SrcSJ")

' message box for testing
' MsgBox "lt: " & lt & vbCrLf & _
"dt: " & dt


' the hyperlink you want
Set h = Doc.Hyperlinks.Add(Anchor:=r, Address:=lt, TextToDisplay:=dt)

' Set mybm to "cover" the inserted link so it is easy to delete the
old hyperlink
Doc.Bookmarks.Add Name:="mybm", Range:=h.Range

Set r = Nothing
Set h = Nothing


End Sub

Thans for posting back - it's useful to have confirmation that this approach
still works.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"FUBARinSFO" wrote in message
...
Peter:

OK, got it now. Some confusion on my part about the instructions, but
"ask the computer" came through with the answer, as usual.

Thanks again.

-- Roy Zider

Public WithEvents App As Word.Application

Private Sub App_MailMergeBeforeRecordMerge(ByVal Doc As Document,
Cancel As Boolean)
Dim dt As String
Dim lt As String
Dim h As Hyperlink
Dim r As Range


' set the range variable to our placeholder bookmark
Set r = Doc.Bookmarks("mybm").Range

' delete any existing text (this is needed for records after record 1)
r.Text = ""

' construct the link text that you want. I'm assuming your data source
' has fields called idfield and namefield.
' lt = "http://www.testsite.com?id=" & _
' Doc.MailMerge.DataSource.DataFields("idfield") & _
' "&name=" & _
' Doc.MailMerge.DataSource.DataFields("namefield")

' my hyperlink is in the local Excel file in field SrcLink2, as the
link text string
lt = Doc.MailMerge.DataSource.DataFields("SrcLink2")

' set up the display text that you want. If it should be the same
' as the link text, do that:
' dt = lt
' my display text is in field SrcSJ (source subject)
dt = Doc.MailMerge.DataSource.DataFields("SrcSJ")

' message box for testing
' MsgBox "lt: " & lt & vbCrLf & _
"dt: " & dt


' the hyperlink you want
Set h = Doc.Hyperlinks.Add(Anchor:=r, Address:=lt, TextToDisplay:=dt)

' Set mybm to "cover" the inserted link so it is easy to delete the
old hyperlink
Doc.Bookmarks.Add Name:="mybm", Range:=h.Range

Set r = Nothing
Set h = Nothing


End Sub

BTW by using this technique you really ought to be able to avoid the probem
of having a separate field with the doubled-up backslashes, by e.g. using
replace() in VBA to replace single backslashes in the field values.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"FUBARinSFO" wrote in message
...
Peter:

OK, got it now. Some confusion on my part about the instructions, but
"ask the computer" came through with the answer, as usual.

Thanks again.

-- Roy Zider

Public WithEvents App As Word.Application

Private Sub App_MailMergeBeforeRecordMerge(ByVal Doc As Document,
Cancel As Boolean)
Dim dt As String
Dim lt As String
Dim h As Hyperlink
Dim r As Range


' set the range variable to our placeholder bookmark
Set r = Doc.Bookmarks("mybm").Range

' delete any existing text (this is needed for records after record 1)
r.Text = ""

' construct the link text that you want. I'm assuming your data source
' has fields called idfield and namefield.
' lt = "http://www.testsite.com?id=" & _
' Doc.MailMerge.DataSource.DataFields("idfield") & _
' "&name=" & _
' Doc.MailMerge.DataSource.DataFields("namefield")

' my hyperlink is in the local Excel file in field SrcLink2, as the
link text string
lt = Doc.MailMerge.DataSource.DataFields("SrcLink2")

' set up the display text that you want. If it should be the same
' as the link text, do that:
' dt = lt
' my display text is in field SrcSJ (source subject)
dt = Doc.MailMerge.DataSource.DataFields("SrcSJ")

' message box for testing
' MsgBox "lt: " & lt & vbCrLf & _
"dt: " & dt


' the hyperlink you want
Set h = Doc.Hyperlinks.Add(Anchor:=r, Address:=lt, TextToDisplay:=dt)

' Set mybm to "cover" the inserted link so it is easy to delete the
old hyperlink
Doc.Bookmarks.Add Name:="mybm", Range:=h.Range

Set r = Nothing
Set h = Nothing


End Sub