Hyperlink in Word calling from an excel spreadsheet

[Benthos]

Ok, I must do a little explaining first.
I have a form, which collects the path of a given Word file and stores it in
an excel cell. Then I have a module in excel transfer the contents of the
cells to bookmarks in a word template file, then saves the word [template]
document in the folder containing the excel file. I would like to the path
of the file to be displayed as a hyperlink in word.

Here is my code for making it a hyperlink:
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim strText As String
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(Range("Data!Word").Offset(0, 1).Value, ,
True, , , , , , , , , True)
wdDoc.Activate
DoEvents
UpdateBookmarkText wdDoc, "wMSProjectLoc",
Range("TestInfo!SMSProjectLoc").Offset(0, 1).Value
Dim MShyperlink As Word.Range
Set MShyperlink = wdDoc.Bookmarks("wMSProjectLoc").Range
wdDoc.Hyperlinks.Add Anchor:=MShyperlink, Address:=MShyperlink,
TextToDisplay:=MShyperlink

wdDoc.SaveAs Filename:=ThisWorkbook.Path & "\Summary.doc"



The update bookmarktext is a command from a differnt part of the module, but
basically, it just puts the text from the excel cell into a designated
bookmark in the word document template.

My problem is this:
When it makes the link, the path is screwed up. It will show up as a link,
but the path given is actually...
C:/mydocs/excelworkbookfolder/FORMTEXT/actuallinkfromcellinexcel.doc

Why does FORMTEXT actually mean, and how do I get the link to be just the
path in the cell, and not that whole long thing. Also, the text displayed
when I don't make it a hyperlink is just the text contained in the cell.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?QmVudGhvcw==?=,

Is this "bookmark" actually a form field, by any chance? FORMTEXT looks like
the field name for a textbox type of form field. Without seeing the code you
don't show us that manipulates the bookmark it's not possible to guess HOW it's
happening. I suspect it might be the result of trying to insert a field
(Hyperlink) into a form field bookmark.

My advice would be to remove the form field and insert a true bookmark,
instead.

Ok, I must do a little explaining first.
I have a form, which collects the path of a given Word file and stores it in
an excel cell. Then I have a module in excel transfer the contents of the
cells to bookmarks in a word template file, then saves the word [template]
document in the folder containing the excel file. I would like to the path
of the file to be displayed as a hyperlink in word.

Here is my code for making it a hyperlink:
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim strText As String
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(Range("Data!Word").Offset(0, 1).Value, ,
True, , , , , , , , , True)
wdDoc.Activate
DoEvents
UpdateBookmarkText wdDoc, "wMSProjectLoc",
Range("TestInfo!SMSProjectLoc").Offset(0, 1).Value
Dim MShyperlink As Word.Range
Set MShyperlink = wdDoc.Bookmarks("wMSProjectLoc").Range
wdDoc.Hyperlinks.Add Anchor:=MShyperlink, Address:=MShyperlink,
TextToDisplay:=MShyperlink

wdDoc.SaveAs Filename:=ThisWorkbook.Path & "\Summary.doc"



The update bookmarktext is a command from a differnt part of the module, but
basically, it just puts the text from the excel cell into a designated
bookmark in the word document template.

My problem is this:
When it makes the link, the path is screwed up. It will show up as a link,
but the path given is actually...
C:/mydocs/excelworkbookfolder/FORMTEXT/actuallinkfromcellinexcel.doc

Why does FORMTEXT actually mean, and how do I get the link to be just the
path in the cell, and not that whole long thing. Also, the text displayed
when I don't make it a hyperlink is just the text contained in the cell.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail