How to create hyperlinked index of sheets in workbook?

See more:

PowerShell

Hi All

I am having a excel workbook with 1000 worksheets in it.

I want to create a index page with link to all these worksheets. so if we click in one like it should navigate to that specific worksheet.

Wrote the below script using ImportExcel module to get all the worksheet names in csv.

Using below code i have created have merged the Index.csv at the existing excel file.

Now Need to Convert the content of the Index sheet to hyperlink.

Please let me know on that

What I have tried:

$Workbook = "C:\Scripts\20210802_ipaddress.xlsx"
$Sheets=@()
$WorkSheetNames=@()
$table =@()

$Excel = New-Excel -Path $Workbook

$Sheets = $Excel | Get-Worksheet

foreach($sheet in $Sheets)
{
   $WorkSheetNames+=$sheet.Name 
}

foreach($data in $WorkSheetNames)
{
    $obj = New-Object PSObject
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "Index" -Value $data
    $table +=$obj
}

$table | Export-Csv -Path "C:\Scripts\Index.csv" -Append -NoTypeInformation

Import-Csv Path "C:\Automation Scripts\IPAM\Index.csv" | Export-Excel -Path "C:\Scripts\\20210812_ipaddress.xlsx" -WorkSheetname 'Index' -MoveToStart