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: http://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.
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.
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.
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.
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.
Looking at the hyperlinks property, we can see all of the hyperlinks that are available/created.
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:firstname.lastname@example.org?subject=PowerShell Rocks", "", "Send an email to Boe", $serverInfoSheet.Cells.Item(2,1).Text ) | Out-Null
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!
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.