Quick Hits: Adding a Hyperlink to Excel Spreadsheet

I had a need a while back to add a few hyperlinks to a couple different excel cells that could be clicked on to take the user either to another portion of the excel spreadsheet or to open up a link to another website.

While you may think that it can be set on the cell itself, it actually resides in a different location (but you still need to remember the cell that you want to put this on).

Rather than go over all of the concepts of working with Excel, I will instead point you to some older articles that I wrote: https://learn-powershell.net/tag/excel/

Let’s assume that we have a spreadsheet already with some data in it already and I need to add some hyperlinks.

image

Yea, it’s not exactly the most useful information but it will work just fine to show this off. My worksheet is saved to $ServerInfoSheet and with that I am going to dig into the Hyperlinks property and make use of the Add method.

$serverInfoSheet.Hyperlinks.Add

image

Now what exactly do these parameters mean? Great question! Let’s go ahead and answer this so we can move forward with adding our hyperlink.

image

Assuming that you want to keep the same text that is already in your cell, you will want to make sure that you supply the cell.text property so that doesn’t change.

$serverInfoSheet.Hyperlinks.Add(
    $serverInfoSheet.Cells.Item(1,1),
    "http://google.com",
    "?gws_rd=ssl#safe=off&q=PowerShell",
    "Give me a Google PowerShell Search!",
    $serverInfoSheet.Cells.Item(1,1).Text
) | Out-Null

I wanted to make full use of each parameter for the sake of demoing this. The first parameter is the cell that this will be adding the hyperlink to while the second parameter is the hyperlink. Because I wanted to show off everything, I added a sub-link (3rd parameter) which will show take us to the search results for PowerShell. The 4th parameter is a little tool tip window that displays a message when you hover over the hyperlink while the last parameter is the actual text which is in the cell (remember that we wanted to keep this to what it already is).

The result is that we now have a hyperlink in place of the plain text in the cell, which in this case will take us to the PowerShell search results in Google.

image

Maybe we want to reference another sheet in the workbook. All we need to do is adjust the hyperlink to point to that particular cell in another workbook.

$serverInfoSheet.Hyperlinks.Add(
    $serverInfoSheet.Cells.Item(2,1),
    "",
    "MoreInformation!A1",
    "Take me another worksheet!",
    $serverInfoSheet.Cells.Item(2,1).Text
) | Out-Null

Note what I used for the 3rd parameter that involves specifying the worksheet name followed by the cell number that the hyperlink will take you to. I also do not have a primary hyperlink for parameter 2. The result is a hyperlink to another worksheet.

imageimage

Looking at the hyperlinks property, we can see all of the hyperlinks that are available/created.

$serverInfoSheet.Hyperlinks

image

Bonus: Email Address

We can even have an email address added that will open up to your default mail client to send an email with a subject.

$serverInfoSheet.Hyperlinks.Add(
    $serverInfoSheet.Cells.Item(3,1),
    "mailto:boeprox@gmail.com?subject=PowerShell Rocks",
    "",
    "Send an email to Boe",
    $serverInfoSheet.Cells.Item(2,1).Text
) | Out-Null

image

Now if we look at the hyperlinks again, we can see both the email address and the subject being display. The trick here is that you use the primary address (2nd parameter) which requires us to prepend mailto: before adding the email address, then following that with a question mark (?) and adding subject: followed by the subject and making sure that we specify the subject. After that, we just have to run the code!

image

So there you have it, a quick way to add some hyperlinks to your Excel spreadsheets using a little bit of PowerShell to hook into the Excel Com object.

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s