MacroFilter: I am trying to make a code snippet for Word work and don't understand why it's not working.

September 10, 2011 10:19 PM

MacroFilter: I am trying to make a code snippet for Word work and don't understand why it's not working.

I want to extract hyperlinks from a Word 2007 document where links have been pasted as text. For example, if I pasted text from a webpage and all the links appeared as "click here", I want to get the value of the URL from those links.

This link sounds like it will do the job quite nicely:

http://technet.microsoft.com/en-us/library/ee692879.aspx

Here is what I have:

---------------------

Sub LinkExtractor()
Set objWord = CreateObject("Word.Application")
objWord.Visible = True

Set objDoc = objWord.Documents.Open("c:\users\calzephyr\desktop\links.docx")

Set colHyperlinks = objDoc.Hyperlinks

For Each objHyperlink In colHyperlinks
Wscript.Echo objHyperlink.Address
Wscript.Echo objHyperlink.TextToDisplay
Next

End Sub

-----------------------

I found out about it from this page, where other snippets were posted, but don't seem to work for me either:

http://www.yourbloghelper.com/2008/12/07/how-to-extract-urls-from-ms-word-2007-hyperlinks/

I think I'm just not seeing where I could be going wrong - can anyone lend me their thoughts as well as their eyeballs? I am wondering if something is missing from the script or if it is not clear that the macro exports the links to another file rather than to itself.

posted by Calzephyr to computers & internet (10 answers total)

Haven't looked too closely, but maybe replace wscript.echo with debug.print. Then press ctrl+g in the editor, and then run the code again and see if anything comes up in the immediate window.
posted by doublehappy at 11:37 PM on September 10, 2011

Yeah, the code you've makes sense but it's for running externally to Word. Instead, open up the VBA editor with ALT+F11, paste this in a new module, press CTRL+G to get the immediate window open, and press F5.

Option Explicit

Sub GetHyperlinks()

Dim doc As Document
Dim h As Hyperlink

Set doc = Documents.Open("filename")

For Each h In doc.Hyperlinks
debug.print h.Address
Next h

End Sub

posted by doublehappy at 11:45 PM on September 10, 2011 [1 favorite]

If you want to export these to a text file, replace "debug.print h.Address" with the following:

Open "c:\urls.txt" For Append As #1
Write #1, h.Address
Close #1

posted by doublehappy at 11:52 PM on September 10, 2011 [1 favorite]

And replace "Write" with "Print" to avoid the " " at each end of the url.
posted by doublehappy at 12:02 AM on September 11, 2011

The original TechNet script is designed to run in VB Script on the command line, rather than within an office VBA macro.
If you save the script in a text file as 'hyper.vbs', you can run it from a command prompt with:
cscript c:\testing\hyper.vbs
posted by Lanark at 2:17 AM on September 11, 2011 [1 favorite]

Thanks Lanark, I see where I got confused. Sometimes when I run across macro stuff it's easy to tell when it's for VB or VBA, but other times there's that piece of the puzzle missing because there just isn't enough info.
posted by Calzephyr at 6:19 AM on September 11, 2011

Doublehappy, that is so sweet! Both methods worked like a charm. Thank you so much!

This is why I love MetaFilter...I learn something new every day!
posted by Calzephyr at 6:47 AM on September 11, 2011

other times there's that piece of the puzzle missing because there just isn't enough info

Microsoft's documentation is voluminous, but it does have a certain impenetrability that reflects that of the systems it's documenting. I think they employ a lot of complicators.

Your clue in this instance is the appearance of the global WScript object, which belongs to the Windows Script Host, not to Office.
posted by flabdablet at 5:08 PM on September 11, 2011

Flabdablet, great link! I have never found Microsoft's website very useful for anything. It seems whenever I find what I need, the link to it is broken. At the same time, I'm impressed with their commitment to backwards compatibility, even if it complicates things.

I liked the article too because as I developed this set of macros for a repetitive task, I found myself trying to find a solution to the wrong question. Is X. Fortunately, I decided to call it a night after I asked my question and realized the path I was heading down.
posted by Calzephyr at 8:02 PM on September 12, 2011

Microsoft's documentation is pretty useless if what you're after is explanations of how to do stuff (every now and again the Scripting Guys will stop being breezy and upbeat for long enough to actually explain something useful, but that's rare). But there is no substitute for it as reference material. Once having penetrated the labyrinth sufficiently to find the name of the Windows feature you're interested in tangling with, I've always found the reference documentation to be reasonably comprehensive, reasonably accurate, and quite well cross-referenced.

It takes a while to get used to the idea that a lot of what you can do with the Windows scripting APIs is almost but not quite language-neutral. Most of them are set up to mesh smoothly with Visual Basic, but VB has completely horrible syntax compared to almost any other scripting language. A couple of years ago, having become completely fed up with ON ERROR RESUME NEXT and piss-poor string handling, I decided I'd never write another line of VB and do all my Windows scripting in JScript instead. So far I've not found any difficulties that can't be worked around, but it's definitely not a short-term path of least resistance - there's lots and lots of stuff for which no JScript examples are available anywhere. The long-term effect has been to force me to understand Windows much better than I did before. Still not sure whether that's a Good Thing.
posted by flabdablet at 7:05 PM on September 13, 2011