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
Permalink
Permalink