Hey, Scripting Guy! Question

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

SpacerHey, Scripting Guy! AnswerScript Center

Hey, AK. You’ll have to forgive us for being a little distracted today. Today is Thursday, October 11th, 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.)

Nobel Prize trivia. The Curie family has been awarded five Nobel Prizes: two to Marie (Chemistry and Physics); one to husband Pierre (Physics); one to daughter Irene Joliet-Curie (Chemistry); and one to son-in-law Frederic Joliet-Curie (Chemistry). Yes, very impressive. However, we would like to point out that the Scripting Guys have just as many Nobel Prizes for Literature as the entire Curie family does.

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:

When we looked at the list of previous winners of the Nobel Prize in Literature, we realized that we hadn’t even heard of most of these people, let alone read any of their works. Selma Ottilia Lovisa Lagerlöf? That’s a cool name, but we never heard of her. As near as we can tell, being a writer no one has ever heard of, and writing things that no one ever reads, greatly increases your chances of winning the Nobel Prize. Well, who’s more unheard of – and more unread – than the Scripting Guys?

The 2000 laureate, Gao XingJian, received his award due, at least in part, to his “bitter insights and linguistic ingenuity.” Granted, we don’t have a lot of insight, and our linguistic ingenuity is limited, at best. But no one is more bitter than the Scripting Guys! (And they’re likely to be even more bitter if they fail to win this year’s Prize.)

One of the co-winners of the 1974 award, Harry Martinson, was cited for “writings that catch the dewdrop and reflect the cosmos.” We have no idea what that means. But we have to believe that the Hey, Scripting Guy! column catches the dewdrop as well as anything.

Well, except maybe an actual dewdrop catcher.

The 1931 Nobel Prize for Literature was given to Erik Axel Karlfeldt, who was actually dead at the time. Come on: even the Scripting Guys can write as well as a dead person, can’t they?

OK: as well as the average dead person.

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.

Note. So what are the other optional parameters available to the Follow method? For more information, take a peek at the Excel VBA Language Reference on MSDN.

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.

Note. The Knut Hamsun FAQ consists of two frequently asked questions: when was he born, and, when did he die.

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….