Click here to Skip to main content
15,077,641 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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
Posted
Updated 12-Aug-21 6:31am
v3

1 solution

From Excel Help:
1.Go to Insert > Link.
2.Under Link to, select Place in This Document.
3.Select the worksheet name and select OK.
   
Comments
Empty Coder 12-Aug-21 5:50am
   
Need to do in powershell not manually
Richard MacCutchan 12-Aug-21 6:57am
   
If you do the above you will see the steps required. And you can always record them in a macro. So once you know the steps you should be able to write the PowerShell script.
Empty Coder 12-Aug-21 9:22am
   
Richard I went through the steps but not able to figure it out that how to get it done in powershell.
Richard MacCutchan 12-Aug-21 9:36am
   
Sorry, but I have not used PowerShell to edit Excel workbooks. I suggest you try Google to find examples.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900