Recently, I had the need to pull out all of the hyperlinks from an excel worksheet that was given to me. While I could have gone the manual approach by looking through all of the cells in the worksheet and copying all of the hyperlinks out manually, that would have been a long grueling thing to do.

PowerShell fortunately gives me all of the ammo I need by allowing the use of COM object creation to hook into the Excel.Application COM object and then open up an existing Excel document and locate all of the hyperlinks which are in the spreadsheet. Automation will always be better than manual if given the opportunity to use it.

Since this is a ‘Quick Hits’ article, let’s not waste any time and step through the process of locating all of the hyperlinks in an Excel document. The first thing that I will do is create the Excel.Application COM object and then open up my existing Excel document using the Open method under the Workbooks property on the Excel object.

#region Load Excel
$excel = New-Object -ComObject excel.application
$excel.visible = $False
$workbook = $excel.Workbooks.Open('C:\users\proxb\desktop\Links.xlsx')
#endregion Load Excel

Note that you will need to supply the full path name to the document, otherwise the open attempt will fail.

From there, I will reference whichever worksheet that has the links I want to pull. In this case,  I only have a single worksheet so I will use the WorkSheet property and specify ‘1’ which says to use the first worksheet in the workbook. If you wanted the second worksheet,  then you would have used a ‘2’. The worksheet object has a property called Hyperlinks which, as you might guess, lists all of the cells with have hyperlinks in them.

$workbook.Worksheets(1).Hyperlinks

As you can see, not only do you get the hyperlinks, but also the display text and the screen tips if you happen to use them.

I want a little more information such as the row and column where each of these hyperlinks reside at. To do this, I will make use of the Range property which contains the cell’s row and column number and add that to my new custom object.

$Hyperlinks = $workbook.Worksheets(1).Hyperlinks
$Hyperlinks | ForEach-Object {
    $Range = $_.Range
    [pscustomobject]@{
        Display = $_.TextToDisplay
        Url = $_.Address
        Screentip = $_.ScreenTip
        Row = $Range.Row
        Column = $Range.Column
    }
}

So there you go. We can quickly pull all of the hyperlinks from an Excel spreadsheet using PowerShell and besides displaying the Url, we can even locate exactly where in Excel the hyperlink is at by looking at the row and column location.