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

    "Give me a Google PowerShell Search!",
) | 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.

    "Take me another worksheet!",
) | 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.

    "mailto:boeprox@gmail.com?subject=PowerShell Rocks",
    "Send an email to Boe",
) | 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.

Posted in powershell | Tagged , , | Leave a comment

Windows PowerShell MVP for Another Year!


I received a great email this morning with the news that I have been renewed for my 3rd MVP award in Windows PowerShell! This is a great honor and I never expect to get this as we go through the same type of evaluations as everyone else who is nominating for this and anything could happen (there are some amazing members of the community out there).

I always strive to put out some great content that I hope people find useful as well as working on my projects, notably PoshRSJob, to provide the community some great things to work with. I have other ideas for new things coming down the pipeline and am looking forward to doing some more speaking gigs, such as one in November with the Omaha user group that I am the co-lead for talking about Pester. I also hope to do a couple other speaking gigs during the course of this year and next year and looking forward to talking PowerShell with all of the great members of the community who not only teach me some amazing things with PowerShell and also allow me the opportunity to show some of the things that I do with PowerShell. It is because of the community that I enjoy talking about PowerShell and that makes you the reason why I received this award. Thank you!

Posted in powershell | Tagged , | 1 Comment

Finding the PowerShellAsyncResult on PowerShell Object After Using BeginInvoke

I’ve been here many of times when working with the console and creating a PowerShell instance and runspace to kick off a runspace in the background. I have everything situated and then call BeginInvoke() against my PowerShell object and realize my mistake when I see the PowerShellAsyncResult being displayed proudly in my console.

$Runspace = [runspacefactory]::CreateRunspace()
$PowerShell = [powershell]::Create()
$PowerShell.Runspace = $Runspace
    #Imagine that this was some crazy long operation
        Name = 'Boe Prox'
        PowerShell = $True


If you’ve worked with this before of done something where you had to use BeginInvoke() and not captured the resulting System.Management.Automation.PowerShellAsyncResult output, you know the pain in having to close out the console and re-running this and making sure to capture the output.

I had mentioned this in my talk on PowerShell runspaces that if you don’t capture this output, then it is the end of the line for your command running in the background.

It is painful. It is frustrating. And it is not the end of the world! There is actually hope for you if this happens and you do not want to kill whatever you are doing to restart the command in the runspace.

The trick lies in using Reflection to look into the PowerShell object for the invokeAsyncResult field and pulling the object out so you can use it later on with EndInvoke().

First we need to get the proper binding flags so we can look for our field. In this case, I only need the nonpublic and instance flags. I already know the name of the field: invokeAsyncResult from performing a all out look at the fields on my object.

Armed with this, I can now pull the field from the object.

$BindingFlags = [Reflection.BindingFlags]'nonpublic','instance'
$Field = $PowerShell.GetType().GetField('invokeAsyncResult',$BindingFlags)


Now all I have to do is get the value of this field. In order to do that, I use the GetValue() method on the field and give it the PowerShell object as the required object in the parameter.

$Handle = $Field.GetValue($PowerShell)


We now have our PowerShellAsyncResult  object back and can now use it to properly end the command using EndInvoke().


Perfect! Now we have a way to save ourselves if we happen to call BeginInvoke() on our PowerShell object and happen to forget to save the output object to use later on!

Posted in powershell | Tagged , , , | 1 Comment

Video and Materials Available From my Talk on PowerShell Runspaces With Mississippi PowerShell User Group

The video from my presentation to the Mississippi PowerShell User Group is now live on YouTube for your viewing pleasure.


The slides and demo code that I used for this presentation is also available at the link below.


I had a great time presenting on a topic that is fun to talk about. I hope to follow up on a couple of questions in the future with a blog article answer to answer them. Please let me know if you have any questions regarding what I talked about!

Posted in powershell | Tagged , , , | Leave a comment

Speaking at Mississippi PowerShell User Group on Runspaces

MSPSUG Virtual Meeting: The Art of PowerShell Runspaces

A week from tonight (8 Sept, 2015) you can find me online speaking at the Mississippi PowerShell User Group talking about The Art of PowerShell Runspaces. This is going to be a great talk and I will be covering a bunch of things on runspaces ranging from stepping into your first runspace build to throttling with runspacepools as well as using runspaces in your UIs. Near the end I will provide a demo of my module: PoshRSJob provides a PSJobs like approach to using runspacepools and runspaces.

And anything else that I can think of!

You can attend the user group by signing up at this link: https://www.eventbrite.com/e/mspsug-virtual-meeting-the-art-of-powershell-runspaces-tickets-18292593640

If you cannot attend, have no fear as this will be recorded and available for viewing on YouTube later on.

Hope to see you there!

Posted in powershell | Tagged , , , | Leave a comment