Use VBA in Excel to Open Word Doc and Create a list of Hyperlinks

I would like to use a routine in Excel to open a Word document from a list
and create a list of the hyperlinks in the document(s).

I can open the word document but I cannot extract the desired hyperlink
information.

Can anyone tell me what is wrong with this code or this methodology?

Sub OpenWordDoc()

Dim WordObj As Object
Dim Fpath As String
Dim LinksList() As Variant
Dim aHyperlink As Hyperlink

Err.Clear
On Error Resume Next

ReDim LinksList(4, 2)

' Set file path
Fpath = "C:\Documents and Settings\UserRoutines\ListHyperlinksTest.doc"

' Open the word document
Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open (Fpath)
WordObj.Visible = True

' This part works in Word but does not work in Excel
i = 0
For Each aHyperlink In ActiveDocument.Hyperlinks
i = i + 1
LinksList(i, 1) = aHyperlink.TextToDisplay
LinksList(i, 2) = aHyperlink.Address
Next aHyperlink
' End of section that works in Word but does not work in Excel

' Close the file
WordObj.Documents.Close
' Quit Word
WordObj.Quit

End Sub

Thanks in advance,
Raul

Raul:

This is just a guess. I think there is no ActiveDocument because your Word
application isn't active. Try creating the document variable explicitly,
like

Dim wdDoc as Object

Set wdDoc = WordObj.Documents.Open(fpath)

For Each aHyperlink in wdDoc.Hyperlinks
etc...
Next aHyperlink

You do set the Word app to visible, so that reasoning may not be right.
Nevertheless, I think having a document object variable will fix it.
Another route might be to qualify the ActiveDocument property like

For Each aHyperlink in WordObj.ActiveDocuments.Hyperlinks

I'm not sure how Excel would know that ActiveDocument is supposed to refer
to the Word object model, other than maybe that it doesn't exist anywhere
but there. BTW, what error are you getting? Compile error?

You may also have a problem with the Hyperlink object. Word has a Hyperlink
object, but so does Excel. If you're going to be using late-binding, you
might want to

Dim aHyperlink as Object

whereas if you were using early-binding, you would use

Dim aHyperlink As Word.Hyperlink

--
**** Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

Raul wrote:
> I would like to use a routine in Excel to open a Word document from a
> list and create a list of the hyperlinks in the document(s).
>
> I can open the word document but I cannot extract the desired
> hyperlink information.
>
> Can anyone tell me what is wrong with this code or this methodology?
>
> Sub OpenWordDoc()
>
> Dim WordObj As Object
> Dim Fpath As String
> Dim LinksList() As Variant
> Dim aHyperlink As Hyperlink
>
> Err.Clear
> On Error Resume Next
>
> ReDim LinksList(4, 2)
>
> ' Set file path
> Fpath = "C:\Documents and
> Settings\UserRoutines\ListHyperlinksTest.doc"
>
> ' Open the word document
> Set WordObj = CreateObject("Word.Application")
> WordObj.Documents.Open (Fpath)
> WordObj.Visible = True
>
> ' This part works in Word but does not work in Excel
> i = 0
> For Each aHyperlink In ActiveDocument.Hyperlinks
> i = i + 1
> LinksList(i, 1) = aHyperlink.TextToDisplay
> LinksList(i, 2) = aHyperlink.Address
> Next aHyperlink
> ' End of section that works in Word but does not work in Excel
>
> ' Close the file
> WordObj.Documents.Close
> ' Quit Word
> WordObj.Quit
>
> End Sub
>
> Thanks in advance,
> Raul

****,
Thanks for the input. The solution was a combination of:
1) Dim aHyperlink As Word.Hyperlink
2) Include the Microsoft Word 10.0 Object Library in the selected references.
3) For Each aHyperlink in WordObj.ActiveDocuments.Hyperlinks

Thanks again,
Raul


"**** Kusleika" wrote:

> Raul:
>
> This is just a guess. I think there is no ActiveDocument because your Word
> application isn't active. Try creating the document variable explicitly,
> like
>
> Dim wdDoc as Object
>
> Set wdDoc = WordObj.Documents.Open(fpath)
>
> For Each aHyperlink in wdDoc.Hyperlinks
> etc...
> Next aHyperlink
>
> You do set the Word app to visible, so that reasoning may not be right.
> Nevertheless, I think having a document object variable will fix it.
> Another route might be to qualify the ActiveDocument property like
>
> For Each aHyperlink in WordObj.ActiveDocuments.Hyperlinks
>
> I'm not sure how Excel would know that ActiveDocument is supposed to refer
> to the Word object model, other than maybe that it doesn't exist anywhere
> but there. BTW, what error are you getting? Compile error?
>
> You may also have a problem with the Hyperlink object. Word has a Hyperlink
> object, but so does Excel. If you're going to be using late-binding, you
> might want to
>
> Dim aHyperlink as Object
>
> whereas if you were using early-binding, you would use
>
> Dim aHyperlink As Word.Hyperlink
>
> --
> **** Kusleika
> MS MVP - Excel
> www.dailydoseofexcel.com
>
> Raul wrote:
> > I would like to use a routine in Excel to open a Word document from a
> > list and create a list of the hyperlinks in the document(s).
> >
> > I can open the word document but I cannot extract the desired
> > hyperlink information.
> >
> > Can anyone tell me what is wrong with this code or this methodology?
> >
> > Sub OpenWordDoc()
> >
> > Dim WordObj As Object
> > Dim Fpath As String
> > Dim LinksList() As Variant
> > Dim aHyperlink As Hyperlink
> >
> > Err.Clear
> > On Error Resume Next
> >
> > ReDim LinksList(4, 2)
> >
> > ' Set file path
> > Fpath = "C:\Documents and
> > Settings\UserRoutines\ListHyperlinksTest.doc"
> >
> > ' Open the word document
> > Set WordObj = CreateObject("Word.Application")
> > WordObj.Documents.Open (Fpath)
> > WordObj.Visible = True
> >
> > ' This part works in Word but does not work in Excel
> > i = 0
> > For Each aHyperlink In ActiveDocument.Hyperlinks
> > i = i + 1
> > LinksList(i, 1) = aHyperlink.TextToDisplay
> > LinksList(i, 2) = aHyperlink.Address
> > Next aHyperlink
> > ' End of section that works in Word but does not work in Excel
> >
> > ' Close the file
> > WordObj.Documents.Close
> > ' Quit Word
> > WordObj.Quit
> >
> > End Sub
> >
> > Thanks in advance,
> > Raul
>
>
>