How to make Excel hyperlinks using PowerShell?

ImportExcel - PowerShell module

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

Last week I have mentioned the new functions waiting to be included into the module. This week I would like to write about another set of functions:

Those are fresh too and were inspired by a query I have got from Garry Bargsley (blog|twitter) who needed adding hyperlinks to the existing cells without using HYPERLINK() Excel function.

How do you add a local hyperlink in Excel? On the Insert tab select Link > Links and one the new window click on Place in This Document. Then select one of the defined names. Set the Text to display and click OK.

Get the functions first

As this is not part of the module, and the new version is not out yet (as of January 13th, 2021) we will need to add the functions manually.

Go to the links above and copy-paste to your editor of choice. Then run each function and after it is all done run this to see the functions are there:

# verify the functions have been loaded
Get-Command -Name "*ExcelHyperlink*"
<#
CommandType     Name
-----------     ----
Function        Add-ExcelHyperlink
Function        Get-ExcelHyperlink
Function        Remove-ExcelHyperlink
#>

Looks great, time to play with our new toys. Although the Pester tests were OK with my new functions be aware there is a room for improvements.

Preparation

Let’s prepare our environment now to make sure the file does not exist.

# set location for the files - I am using the temporary folder in my user profile
Set-Location $env:TEMP

# that is the location of our files
Invoke-Item $env:TEMP

# cleanup any previous files - this helps in case the example has been already ran
$excelFiles = "ImportExcelHowTo004.xlsx"
Remove-Item $excelFiles -ErrorAction SilentlyContinue

As an example we will use a workbook with two worksheets: one with links and the other one with some random data.

# create a file with two worksheets and open it
'Range on this sheet','Range on another sheet' | Export-Excel -Path $excelFiles -WorksheetName HowToHyperlink
1..100 | Export-Excel -Path $excelFiles -WorksheetName Data -KillExcel -Show

Create named ranges within the workbook

We need to create named ranges – to do so we can use Excel itself:
Formulas > Defined Names > Define Name or….

…PowerShell. In order to create name within sheet scope we can use Add-ExcelName function, but to create one within workbook scope we need to go a bit deeper (potentially something that can be added in the next release to the Add-ExcelName function).

# open Excel file with PowerShell
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# get the worksheets
$excel = $excelPackage.Workbook.Worksheets['HowToHyperlink']
$excel2 = $excelPackage.Workbook.Worksheets['Data']

# add named ranges to the worksheets locally i.e. only visible within worksheet they exist in
Add-ExcelName -Range $excel.Cells['D1:G20'] -RangeName 'NamedRangeSheetEmpty'
Add-ExcelName -Range $excel2.Cells['A1:A100'] -RangeName 'NamedRangeSheetData'

# add named range to the workbook - visible from the entire file
$excelPackage.Workbook.Names.Add('NamedRangeWorkbook',$excel2.cells['D1:F50'])

Close-ExcelPackage -ExcelPackage $excelPackage -Show

As we can see, the top two names are visible within relevant worksheets, whereas the bottom one is visible in the whole workbook. The difference is – we won’t be able to refer to named range in Data from HowToHyperlink and vice-versa, where the one in the workbook scope can be linked from the entire document.

Perfect, now we are ready to add some hyperlinks.

Add new hyperlink in Excel with PowerShell

First, let’s add the hyperlinks without setting a custom display text. One thing to note, the functions are written this way that Open/Close-ExcelPackage are embedded so there is no need to open it separately.

# add hyperlinks to existing named ranges - keeping the original cell's text
Add-ExcelHyperlink -Path $excelFiles -WorksheetName HowToHyperlink -Cell A1 -Hyperlink NamedRangeSheetEmpty
Add-ExcelHyperlink -Path $excelFiles -WorksheetName HowToHyperlink -Cell A2 -Hyperlink NamedRangeSheetData -Show

As a result we will get previous cells with hyperlink formatting (underlined blue text). As we can see the cell value is not using HYPERLINK() Excel function.

First link works without problems, but the other one will fail, as it leads to the named range that is in the scope of a different worksheet. Hence the need of a named range within workbook scope.

Add hyperlinks with (or without) the custom name to an empty cell

There might be a case we want to add a hyperlink to an empty cell – then it would be tricky to click the the linked space character.

There are two ways to deal with it:

# add hyperlinks to existing named ranges - to an empty cell - with a display name
Add-ExcelHyperlink -Path $excelFiles -WorksheetName HowToHyperlink -Cell A4 -Hyperlink NamedRangeWorkbook -DisplayName 'Click here to see puppies'

# and without name
Add-ExcelHyperlink -Path $excelFiles -WorksheetName HowToHyperlink -Cell A5 -Hyperlink NamedRangeWorkbook -Show

Two new links have been added, the first one with the text we’ve provided and the second one with a generic ‘Link’.

Let’s move on to the next function – Get-ExcelHyperlink.

See Excel hyperlinks with PowerShell

To see existing hyperlinks within document (I have added few links manually in the Excel as the previous command does not support adding those yet) run the below command:

# get all the links in the workbook...
Get-ExcelHyperlink -Path $excelFiles

<#
Worksheet      Cell StyleName Hyperlink                                                
---------      ---- --------- ---------                                                
HowToHyperlink A1   Hyperlink xl://internal/                                           
HowToHyperlink A2   Hyperlink xl://internal/                                           
HowToHyperlink A4   Hyperlink xl://internal/                                           
HowToHyperlink F4   Hyperlink New%20document.xlsx                                      
HowToHyperlink A5   Hyperlink xl://internal/                                           
HowToHyperlink F5   Hyperlink mailto:mikeybronowski@gmail.com?subject=I have a question
HowToHyperlink F7   Data `    Google.com                                               
HowToHyperlink H7   Hyperlink xl://internal/   
#>

# ... or get those in a specific workbook...
Get-ExcelHyperlink -Path $excelFiles -WorksheetName Data

# ... or cell
Get-ExcelHyperlink -Path $excelFiles -WorksheetName HowToHyperlink -Cell A4

Seen them all? Now, remove all the hyperlinks.

Remove Excel hyperlinks with PowerShell

The function to remove the hyperlink leaves the value in the cell untouched plus removes the formatting.

# remove specific hyperlink
Remove-ExcelHyperlink -Path $excelFiles -WorksheetName HowToHyperlink -Cell F7

# or remove them all
Remove-ExcelHyperlink -Path $excelFiles

After removing all the hyperlinks the spreadsheet looks pretty boring:

Summary

The functions did the job for me, but I understand there is more work to be done so they are more flexible and universal. Hopefully, that will be part of another blog post in the future.

Thank you,
Mikey

Leave a Reply

%d bloggers like this: