{"id":63823,"date":"2007-10-11T22:27:00","date_gmt":"2007-10-11T22:27:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/10\/11\/hey-scripting-guy-how-can-i-retrieve-hyperlink-information-from-an-office-excel-spreadsheet\/"},"modified":"2007-10-11T22:27:00","modified_gmt":"2007-10-11T22:27:00","slug":"hey-scripting-guy-how-can-i-retrieve-hyperlink-information-from-an-office-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-retrieve-hyperlink-information-from-an-office-excel-spreadsheet\/","title":{"rendered":"Hey, Scripting Guy! How Can I Retrieve Hyperlink Information From an Office Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, <\/H2>\n

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<\/B>. How can I write a script that retrieves the underlying email address?

— AK<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, AK. You\u2019ll have to forgive us for being a little distracted today. Today is Thursday, October 11th<\/SUP>, the day that the winner of the 2007 Nobel Prize for Literature<\/B><\/A> is announced. Admittedly, your first thought is probably, \u201cBut you guys write a daily scripting column on TechNet; you don\u2019t have a prayer<\/I> of winning the Nobel Prize for Literature.\u201d 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.) <\/P>\n\n<\/THEAD>\n\n\n
\n

Nobel Prize trivia<\/B>. The Curie family has been awarded five<\/I> 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.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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:<\/P>\n\n\n\n\n\n\n
\u2022<\/TD>\n\n

When we looked at the list of previous winners of the Nobel Prize in Literature, we realized that we hadn\u2019t even heard of most of these people, let alone read any of their works. Selma Ottilia Lovisa Lagerl\u00f6f? That\u2019s 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\u2019s more unheard of \u2013 and more unread \u2013 than the Scripting Guys?<\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

The 2000 laureate, Gao XingJian, received his award due, at least in part, to his \u201cbitter insights and linguistic ingenuity.\u201d Granted, we don\u2019t 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\u2019re likely to be even more bitter if they fail to win this year\u2019s Prize.)<\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

One of the co-winners of the 1974 award, Harry Martinson, was cited for \u201cwritings that catch the dewdrop and reflect the cosmos.\u201d 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<\/B><\/A>.<\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

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\u2019t they?

OK: as well as the average<\/I> dead person.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

At any rate, we intend to spend most of the day sitting around waiting for the phone ring. But you know what? Why don\u2019t 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\u2019s question and, on the off chance that we fail<\/I> to win the Nobel Prize this year, make a pretty compelling case of us being awarded the prize in 2008.<\/P>\n

Here\u2019s the script, AK (and members of the Swedish Academy):<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
Set colLinks = objWorksheet.Hyperlinks<\/p>\n
For Each objLink in colLinks\n    Wscript.Echo “Displayed text: ” & objLink.TextToDisplay\n    Wscript.Echo “Hyperlink: ” & objLink.Address\n    Wscript.Echo\nNext\n<\/PRE>\n
So how does this script work? Hey, don\u2019t ask us; ask Orhan Pamuk, the 2006 Nobel Prize winner in Literature. Let\u2019s see whether or not you really deserved<\/I> that prize, Orhan. <\/P>\n
Oh. Uh, thanks. Apparently, we start things out by creating an instance of the Excel.Application<\/B> object and then set the Visible<\/B> 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:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
After we\u2019ve 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:<\/P>
Set colLinks = objWorksheet.Hyperlinks\n<\/PRE>\n
So what do we do next? Hold on a second; we\u2019ll have to check with Orhan \u2026.<\/P>\n
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<\/B> and Address<\/B> properties:<\/P>
Wscript.Echo “Displayed text: ” & objLink.TextToDisplay\nWscript.Echo “Hyperlink: ” & objLink.Address\n<\/PRE>\n
In turn, that\u2019s going to give us output similar to this:<\/P>
Displayed text: Hey, Scripting Guy!\nHyperlink: http:\/\/www.microsoft.com\/technet\/scriptcenter\/resources\/qanda\/default\n.mspx<\/p>\n
Displayed text: Yes\nHyperlink: mailto:scripter@microsoft.com?subject=Test\n<\/PRE>\n
And you\u2019re right: email addresses come out looking a little weird, don\u2019t they? If that\u2019s a problem, try using this For Each loop instead (which we won\u2019t bother to explain in any detail):<\/P>
For Each objLink in colLinks\n    Wscript.Echo “Displayed text: ” & objLink.TextToDisplay\n    strLink = objLink.Address\n    strLink = Replace(strLink, “mailto:”, “”)\n    arrLinks = Split(strLink, “?”)\n    Wscript.Echo “Hyperlink: ” & arrLinks(0)\n    Wscript.Echo\nNext\n<\/PRE>\n
All we\u2019re doing in that block of code is using the Replace<\/B> function to replace any instances of the string value mailto:<\/I>. We then use the Split<\/B> 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\u2019t have<\/I> 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:<\/P>
scripter@microsoft.com\n<\/PRE>\n
And there you have it.<\/P>\n
You know what? You\u2019re right: Ernest Hemingway didn\u2019t 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\u2019t know that for sure. But probably.) Therefore, let\u2019s show you another cool little trick you can do with hyperlinks in an Excel spreadsheet. Here\u2019s a script that goes through a worksheet, extracts all the hyperlinks, and then opens each of those links in a new window:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
Set colLinks = objWorksheet.Hyperlinks<\/p>\n
For Each objLink in colLinks\n    objLink.Follow(True)\nNext\n<\/PRE>\n
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\u2019re calling the Follow<\/B> method to open each of those links in a new window:<\/P>
For Each objLink in colLinks\n    objLink.Follow(True)\nNext\n<\/PRE>\n
How do we know each of these links will open in a new window? Because we set the NewWindow<\/B> parameter (the first of several optional parameters available to the Follow method) to True. <\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. So what are<\/I> the other optional parameters available to the Follow method? For more information, take a peek at the Excel VBA Language Reference<\/B><\/A> on MSDN.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Why don\u2019t 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<\/I>. <\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. The Knut Hamsun FAQ<\/B><\/A> consists of two frequently asked questions: when was he born, and, when did he die.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Here\u2019s a script that retrieves all the hyperlinks from a spreadsheet and then adds those links to your Internet Explorer Favories:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
Set colLinks = objWorksheet.Hyperlinks<\/p>\n
For Each objLink in colLinks\n    objLink.AddToFavorites\nNext\n<\/PRE>\n
As you can see, this is remarkably similar to our previous script; the only difference is that, this time around, we called the AddToFavorites<\/B> method rather than the Follow method.<\/P>\n
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\u2026.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019ll have to forgive us for being a little distracted today. Today is Thursday, […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,49,3,5],"class_list":["post-63823","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019ll have to forgive us for being a little distracted today. Today is Thursday, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63823","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=63823"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63823\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=63823"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63823"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63823"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}