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

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

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

--
Dick 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

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

Dick,
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


"Dick 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

--
Dick 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