Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a bulleted list within a cell in excel (as in Word) | New Users to Excel | |||
How to open Excel list in Word for mail merge/labels? | Excel Discussion (Misc queries) | |||
How do I create a list (Word) of the names on Excel worksheet tabs | Excel Worksheet Functions | |||
Create a list box/combo box with hyperlinks | Excel Worksheet Functions | |||
Possible to open word documents in Excel using hyperlinks? | New Users to Excel |