How Can I Add a Hyperlink to an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! How can I add a hyperlink to an Excel spreadsheet?

— J B-N

Hey, J B-N. To begin with, we’d like to point out that we’re well aware of all the recent media scandals involving such prestigious organizations as The New York Times. As you know, many of those scandals revolve around writers who didn’t actually do the work they claimed they did. We wanted to take a moment to set everyone’s mind at ease: the Scripting Guys would never take shortcuts in an attempt to fool our readers into thinking we did work that we didn’t really do.

OK, OK, we admit it: we really didn’t do any work when it came time to answer this question. Awhile back we answered a similar question about adding a hyperlink to a Microsoft Word document. In order to answer this question, we pretty much took the script we wrote for that column, crossed out Microsoft Word and wrote in Microsoft Excel. We are suitably ashamed and embarrassed and we promise that something like this will never happen again.

Well, not unless someone wants to know how to add a shortcut to a PowerPoint presentation ….

Admittedly, we didn’t exactly knock ourselves out for today’s column; on the bright side, however, everyone gets a useful little script out of the deal. If you want to add a hyperlink to an Excel spreadsheet here’s what you need to do:

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

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1, 1) = “Script Center”
Set objRange = objExcel.Range(“A1”)
Set objLink = objWorksheet.Hyperlinks.Add _
    (objRange, “http://www.microsoft.com/technet/scriptcenter”)

As you can see, we begin by creating an instance of the Excel.Application object and then setting the Visible property to True; that gives us a running instance of Microsoft Excel that we can see onscreen. Next we use these two lines of code to create a new workbook and a new worksheet:

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

Once we have that worksheet we then use this line of code to add the term “Script Center” to cell A1 (that is, cell row 1, column 1):

objWorksheet.Cells(1, 1) = “Script Center”

As you probably figured out, we’re going to use Script Center as the hyperlink text.

OK, so what about the hyperlink? Well, to begin with, we need to create an instance of the Excel Range object, an instance that will encompass the hyperlink text. Because we want to hyperlink to appear in cell A1 we create a range that consists solely of cell A1:

Set objRange = objExcel.Range(“A1”)

All that’s left now is to call the Add method and add a new link to the Hyperlinks collection. Here’s how we do that:

Set objLink = objWorksheet.Hyperlinks.Add _
    (objRange, “http://www.microsoft.com/technet/scriptcenter”)

Pretty simple, huh? We just call the Add method and pass it two parameters: the object reference for our Range object (objRange) and the URL for the hyperlink (http://www.microsoft.com/technet/scriptcenter). You’ll now be able to click on cell A1 and transport yourself to the Script Center.

Yes, exactly like the transporters on the old Star Trek TV show.

OK, OK, we admit it: it’s nothing at all like the transporters on the old Star Trek TV show. You guys are a tough audience; if this keeps up we might have to start telling the truth once in awhile. With that in mind, the Scripting Guys would like to issue the following corrections: Dean Tsaltas is from Canada, not Pluto. Peter Costantini isn’t 537 years old (not till April anyway) and Jean Ross is not a talking squirrel. Finally, Greg Stemp did not win the 1937 Miss Nebraska beauty pageant; in fact, he did not even place in the top 10.

We hope you’re satisfied now.

ScriptingGuy1

Follow