Set object to Word doc opened in Excel VBA?

[Ed]

Excel VBA actuates a hyperlink which opens a Word document. At this point,
no Word application or document objects are set. Is it possible to
"capture" the document being opened by the hyperlink and set an object
reference to it in Excel VBA?

Ed

 

[Peter]

Instead of using a hyperlink to open the document, add a reference to "Microsoft Word XX.X Object Library" to your project (Tools | References | scroll down the list and check the checkbox), then use the following:


Dim oWord As New Word.Application
Dim oDoc As Word.Document

' open a document invisibly
Set oDoc = oWord.Documents.Open(Filename:="my word.doc", Visible:=False)

' manipulate oDoc

Call oDoc.Close(SaveChanges:=True)
Call oWord.Quit(SaveChanges:=False)

Set oDoc = Nothing
Set oWord = Nothing

Once you're done developing, replace

Dim oWord As New Word.Application
Dim oDoc As Word.Document

with

Dim oWord As Object
Dim oDoc As Object
Set oWord = CreateObject("Word.Application")

and remove the Reference. This will remove a dependency upon a particular version of Word. Use the reference first to take advantage of IntelliSense.

hth,

-Peter

 

[Peter]

How do you link that code to the Excel cells containing the links?

That is an exercise left up to the reader. ;-)

Actually, since he posted, "Excel VBA actuates a hyperlink," I thought perhaps he was triggering a hyperlink programatically, and could just replace that with the code, substituting ActiveSheet.Hyperlinks(x).Address (or similar) for "my word.doc".

Otherwise, I don't know how to hook the hyperlink clicking before the target is activated. SheetFollowHyperlink and FollowHyperlink are both invoked after the target is activated. There might be some API event hook he could use.

Another possibility, although it's more complex, is the following:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctgetobjectx.asp
beware wrapping.
That can be integrated into FollowHyperlink...

Any better ideas?

hth,

-Peter