Inserting multiple hyperlinks in Word from Excel VBA

[Ariel]

I have the following code working fine to insert one hyperlink, however, (1)
I can't get the cursor to move to the end of the inserted hyperlink, and (2)
I can't get figure our how to insert another hyperlink (or several more)
after the first one.

Any help is appreciated.

Sub Hyper ()
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Set WordApp = New Word.Application
Set WordDoc = WordApp.Documents.Add

WordApp.Visible = True

With WordDoc

Dim rng As Word.Range
Set rng = .Range

.Hyperlinks.Add Anchor:=rng, Address:= _
"http://www.caxvd.com/", SubAddress:="", _
ScreenTip:="", TextToDisplay:="Treatment"
End With


End Sub

 

[Shauna Kelly]

Hi Ariel

This code

Set WordApp = New Word.Application
Set WordDoc = WordApp.Documents.Add

WordApp.Visible = True

is creating a new instance of Word and a document in that instance. If this
code is running from within Word, then that seems a bit odd. Is there some
reason you need to create a new instance of Word?


This code

With WordDoc

Dim rng As Word.Range
Set rng = .Range

.Hyperlinks.Add Anchor:=rng, Address:= _
"http://www.caxvd.com/", SubAddress:="", _
ScreenTip:="", TextToDisplay:="Treatment"
End With

is setting rng to be the Range of the whole document. It then adds a
hyperlink with the Anchor being rng, which is to say the Anchor is the range
of the whole document. If you then tried .Hyperlinks.Add to add another
hyperlink, with the same anchor, then it would merely replace the existing
one.

You may need to do something like this:

.Hyperlinks.Add Anchor:=rng, Address:= _
"http://www.caxvd.com/", SubAddress:="", _
ScreenTip:="", TextToDisplay:="Treatment"

rng.Collapse wdCollapseEnd

.Hyperlinks.Add Anchor:=rng, Address:= _
"http://www.somethingelse.com/", SubAddress:="", _
ScreenTip:="", TextToDisplay:="Something else"



Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Helmut Weber]

Hi Ariel,

like this, adapted to my testing environment:

Sub test6789()
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim WordRng As Word.Range
Dim lCnt As Long
Set WordApp = GetObject(, "Word.Application")
Set WordDoc = WordApp.ActiveDocument

With WordDoc
For lCnt = 1 To 10
Set WordRng = WordDoc.Range
WordRng.Collapse Direction:=wdCollapseEnd
.Hyperlinks.Add Anchor:=WordRng, Address:= _
"http://www.caxvd.com/", SubAddress:="", _
ScreenTip:="", TextToDisplay:=Format(lCnt, "000")
Next
End With

End Sub

Of course, this inserts the same hyperlink 10 times.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Ariel]

Hi Shauna,

First off, this code is being run from Excel because there are several
analyses that are being conducted there. The results are then exported to
Word in a new document.

By the time this particular code runs (adding the hyperlinks) alot has been
created, so these dim and set statements have been put at the beginning of
the macro. I am not sure what specific statements need to be added just
before this hyperlinks code.

Secondly, I tried the code you gave me, and there are a couple of things
that need to be fixed (this is a problem with Helmut's code as well):

(1) Between hyperlinks, there needs to be a couple of spaces or a tab. I
tried .TypeText Text:=vbTab but apparently I need to have some "With"
statement imbedded within this section to make it work? Maybe there is an
easier way?

(2) Even with the "rng.Collapse wdCollapseEnd", the cursor remains at the
beginning of the first hyperlink. I will need to continue on adding
paragraphs after this hyperlink thing is completed. How do I get the cursor
to move to the end so that I can vbCr?

Thanks for your help.

Ariel

 

[Ariel]

Hi Shauna,

In response to your question about a new instance of Word and a new
document, this macro is being run from Excel in which analyses are performed
and the results sent to a new word document (a new document will be created
for each individual analysis).

As for the hyperlink code, I tried running two instances of the
hyperlink.add code and it worked, however the two initial problems remain:

(1) There needs to be a space (a space or tab) between added hyperlinks so
that they appear separately.
(2) Even with the “rng.Collapse wdCollapseEnd†the cursor remains at the
beginning of the first hyperlink. I will need to create additional paragraphs
after this section?

As always, thanks for your help!

Ariel

 

[Helmut Weber]

Hi Ariel

try this:

Sub test6789a()
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim WordRng As Word.Range
Dim lCnt As Long
Set WordApp = GetObject(, "Word.Application")
Set WordDoc = WordApp.ActiveDocument

With WordDoc
For lCnt = 1 To 10
' choose one of your liking
' ActiveDocument.Range.InsertAfter vbCrLf
' ActiveDocument.Range.InsertAfter vbTab
' ActiveDocument.Range.InsertAfter chr(32)
Set WordRng = WordDoc.Range
WordRng.Collapse Direction:=wdCollapseEnd
.Hyperlinks.Add Anchor:=WordRng, Address:= _
"http://www.caxvd.com/", SubAddress:="", _
ScreenTip:="", TextToDisplay:=Format(lCnt, "000")
Next
End With

End Sub

(2) Even with the “rng.Collapse wdCollapseEnd” the cursor remains at the
beginning of the first hyperlink.

That's the gist of the matter with using ranges.
The cursor isn't moved at all,
as there is no need for it.
More speed, easier control of what do I do where in the doc.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Ariel]

Hi Helmut,

I tried all of these options you gave me, and they solve only the problem of
placing the cursor at the end of the string of hyperlinks. However, there are
no spaces or tabs between the hyperlinks (they are all run together).

I agree with you on the problems with using the range statement. Is there a
better way of doing this? In Excel VBA, we would either redim the range, or
have move to the next range of cells? I don't know how to do that for Word,
running from Excel?

Thanks

Ariel

 

[Shauna Kelly]

Hi Ariel

1. This code
Set WordRng = WordDoc.Range
sets WordRng to cover the whole range of the document.

This code
WordRng.Collapse Direction:=wdCollapseEnd
collapses the range so that WordRng is now a single point, at the end of the
document.

2. If you want to put a space or tab between the hyperlinks then use one of
the lines of code that Helmut provided for you. Just un-comment the one you
need.

You might like to look up the Range object in the VBA Help files, and read
up on the Properties and Methods of the range object.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Ariel]

Hi Shauna,

None of the lines of code that Helmut gave me spaced out the hyperlinks.

 

[Shauna Kelly]

Hi Ariel

These lines in Helmut's code:

' choose one of your liking
' ActiveDocument.Range.InsertAfter vbCrLf
' ActiveDocument.Range.InsertAfter vbTab
' ActiveDocument.Range.InsertAfter chr(32)

have all been commented out by adding the ' sign to the beginning of the
line.

You could remove the ' sign for one of those lines. If you choose the first,
it will insert a paragraph break, the second inserts a tab and the third
inserts a space before the next hyperlink.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word