Hey, Scripting Guy! How Can I Retrieve Hyperlink Information From an Office Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! I have an Office Excel document that includes a hyperlink linked to an email address. The text in the spreadsheet simply says Yes. How can I write a script that retrieves the underlying email address?

— AK

Hey, AK. You’ll have to forgive us for being a little distracted today. Today is Thursday, October 11^th, the day that the winner of the 2007 Nobel Prize for Literature is announced. Admittedly, your first thought is probably, “But you guys write a daily scripting column on TechNet; you don’t have a prayer of winning the Nobel Prize for Literature.” And, to be honest, that was our first thought as well; after all, very few daily scripting columnists have ever won the Nobel Prize. (No, not even Marie Curie; her two Nobel Prizes were actually in Physics and Chemistry.)

Once we started looking into the history of the Nobel Prize, however, well, suddenly we started to get our hopes up a bit. For example:

At any rate, we intend to spend most of the day sitting around waiting for the phone ring. But you know what? Why don’t we go ahead and see if we can write a script that returns information about the hyperlinks found in an Excel spreadsheet. That would answer AK’s question and, on the off chance that we fail to win the Nobel Prize this year, make a pretty compelling case of us being awarded the prize in 2008.

Here’s the script, AK (and members of the Swedish Academy):

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set colLinks = objWorksheet.Hyperlinks
For Each objLink in colLinks
    Wscript.Echo “Displayed text: ” & objLink.TextToDisplay
    Wscript.Echo “Hyperlink: ” & objLink.Address
    Wscript.Echo
Next

So how does this script work? Hey, don’t ask us; ask Orhan Pamuk, the 2006 Nobel Prize winner in Literature. Let’s see whether or not you really deserved that prize, Orhan.

Oh. Uh, thanks. Apparently, we start things out by creating an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Excel that we can see onscreen. We then use these two lines of code to open the file C:\Scripts\Test.xls, and to bind to the first worksheet in that file:

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)

After we’ve created an object reference to the worksheet we can then retrieve a collection of all the hyperlinks found on that sheet simply by running this line of code:

Set colLinks = objWorksheet.Hyperlinks

So what do we do next? Hold on a second; we’ll have to check with Orhan ….

OK, next we set up a For Each loop to loop through the collection of hyperlinks. Inside that loop, all we have to do is echo back the value of the TextToDisplay and Address properties:

Wscript.Echo “Displayed text: ” & objLink.TextToDisplay
Wscript.Echo “Hyperlink: ” & objLink.Address

In turn, that’s going to give us output similar to this:

Displayed text: Hey, Scripting Guy!
Hyperlink: http://www.microsoft.com/technet/scriptcenter/resources/qanda/default
.mspx

Displayed text: Yes
Hyperlink: mailto:scripter@microsoft.com?subject=Test

And you’re right: email addresses come out looking a little weird, don’t they? If that’s a problem, try using this For Each loop instead (which we won’t bother to explain in any detail):

For Each objLink in colLinks
    Wscript.Echo “Displayed text: ” & objLink.TextToDisplay
    strLink = objLink.Address
    strLink = Replace(strLink, “mailto:”, “”)
    arrLinks = Split(strLink, “?”)
    Wscript.Echo “Hyperlink: ” & arrLinks(0)
    Wscript.Echo
Next

All we’re doing in that block of code is using the Replace function to replace any instances of the string value mailto:. We then use the Split function to split the hyperlink on the question mark; that will separate the actual email address from the Subject line. (What if this is a plain old URL and the address doesn’t have a question mark in it? No problem; the resulting array will simply be a one-item array, with that lone item being the URL.) After that we simply echo back the value of the first item in the array, which should be something like this:

scripter@microsoft.com

And there you have it.

You know what? You’re right: Ernest Hemingway didn’t sit down, write one simple little script, and then get handed a Nobel Prize. (Is that also true for Lucila Godoy Y Alca-Yaga? To be honest, we don’t know that for sure. But probably.) Therefore, let’s show you another cool little trick you can do with hyperlinks in an Excel spreadsheet. Here’s a script that goes through a worksheet, extracts all the hyperlinks, and then opens each of those links in a new window:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set colLinks = objWorksheet.Hyperlinks
For Each objLink in colLinks
    objLink.Follow(True)
Next

The only difference between this script and the first one we showed you takes place in the For Each loop. In our original For Each loop, we echoed back property values for each link in the worksheet. This time, we’re calling the Follow method to open each of those links in a new window:

For Each objLink in colLinks
    objLink.Follow(True)
Next

How do we know each of these links will open in a new window? Because we set the NewWindow parameter (the first of several optional parameters available to the Follow method) to True.

Why don’t we do one more, just to be on the safe side? After all, even Knut Pedersen Hamsun wrote more than just The Growth of the Soil.

Here’s a script that retrieves all the hyperlinks from a spreadsheet and then adds those links to your Internet Explorer Favories:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set colLinks = objWorksheet.Hyperlinks
For Each objLink in colLinks
    objLink.AddToFavorites
Next

As you can see, this is remarkably similar to our previous script; the only difference is that, this time around, we called the AddToFavorites method rather than the Follow method.

That should do it, AK. And now we just sit and wait for the phone to ring. Should be any time now. Yep, any time the old phone will be ringing off the hook. Any time now….

ScriptingGuy1

Follow