creating a hyperlink in a word document from Excel

[Ariel]

I am creating a report in Word that is written in Excel (due to a large
number of actions that needs to occur there).

In any case, I need to insert hyperlinks in the document, and the following
code does not work. WordApp has already been dim'ed:

With WordApp.ActiveDocument
.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
"http://www.ohsu.edu/hms/survey/activity/brief", SubAddress:="", _
ScreenTip:="", TextToDisplay:="Activity"
End With

 

[Tony Jollans]

I suspect that Selection.Range refers to Excel's Selection, and you want
Word's - you must qualify it.

 

[Ariel]

Actually, this was recorded in Word, so selection.range is germaine to Word
(however, when recorded in Excel the code is identical).

Any ideas?

 

[Tony Strazzeri]

What error are you actually getting?

You say "WordApp has already been dim'ed" but has it been "set" ?
ie does it have a value (corresponding to the running word application
object that has the open document).


Cheers
TonyS.

 

[Ariel]

Hi Tony,

Yes, at the start of the sub routine I have the following:

Dim WordApp As Word.Application
Set WordApp = New Word.Application
Dim WordDoc As Word.Document

Everything else works up until the point (plenty of lines of code) where I
want to insert this hyperlink. The error I get is the following: Object
doesn't support this property or method (Error 438)

Any help is appreciated.

Ariel

 

[Tony Jollans]

When you record code in Word it has implicit references to Word objects. If
you copy that code to Excel you must make the implicit references explicit.
Have you tried qualifying the Selection reference?

When you record code in Excel it has implicit references to Excel objects.
The fact that code with implicit references to Word and code with implicit
references to Excel are superficially the same is little more than
coincidence.

 

[Ed]

Two thoughts:
-- Don't use ActiveDocument when you already have WordDoc as a document
object reference
-- Dim a range as Word.Range and Set it to the place you need the hyperlink
installed, and use that reference instead of "Selection".

Depending on what else is going on, "ActiveDocument" and "Selection" can
give you many headaches!
Ed

 

[Tony Strazzeri]

Hi Ariel,


I tried your code both from Word and Excel. I was getting slightly
different error. I am not sure how Word's selection method canwork when
automating the application from outside Word so have substituted a
Range variable instead.

The code below may help you. It works from Excel.


Dim WordApp As Word.Application
Dim WordDoc As Word.Document

Set WordApp = New Word.Application

'This allows you to see the application
WordApp.Visible = True

'This creates a document in the new Application process
Set WordDoc = WordApp.Documents.Add

With WordDoc 'WordApp.ActiveDocument
'playing around here to demonstrate moving around the doc
WordDoc.Range.InsertAfter "aaaaaaa" & vbCr & "bbbbbbbbbbbbb" &
vbCr
Dim rng As Range
Set rng = .Range
rng.Collapse wdCollapseStart
rng.MoveStartUntil "b"
.Hyperlinks.Add Anchor:=rng, _

Address:="http://www.ohsu.edu/hms/survey/activity/brief", _
SubAddress:="", _
ScreenTip:="tip", _
TextToDisplay:="Activity"
End With

Once you have the above working you should be able to substitute the
range with say a bookmark range in order to insert your link where you
want it.

Hope this helps.

Cheers
TonyS.

 

[Ariel]

Hi Tony,

I tried your code and I get a "type mismatch" error at Set rng = .Range

thanks

Ariel

 

[Helmut Weber]

Hi Ariel,

try:

Dim rng As word.Range

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[Ariel]

Works like a charm. Thank you all for your input. It is great having such
support!

 

[Ariel]

Hi Tony,

I have been trying unsuccessfully to get the cursor to move past the
inserted hyperlink so that I can add in another one (or a series of them if
necessary) and then continue on with a new paragraph.

Do you have a suggestion of how I can do this (remember that this code is
being written in Excel VBA).

Thanks for your help!

Ariel