Create a table of contents for sheet names in an Excel book.(Power Automate Desktop)


Japanese version.

Introduces how to create a table of contents for sheet names.

Overall view of Flow

Robin(for copy and paste)

It can be copied and pasted into Power Automate Desktop.

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Test\\Test.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
SET IndexSheetName TO $'''Index'''
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: IndexSheetName
Excel.GetAllWorksheets Instance: ExcelInstance Worksheets=> SheetNames
SET Row TO 1
LOOP FOREACH CurrentItem IN SheetNames
    IF CurrentItem <> IndexSheetName THEN
        Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''=HYPERLINK(\"#%CurrentItem%!A1\",\"%CurrentItem%\")''' Column: 1 Row: Row
        Variables.IncreaseVariable Value: Row IncrementValue: 1
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

Flow creation steps

In the example, it is created on the Index sheet.

Set up Launch Excel Action.

Launch Exceland open the following document
Document pathThe path of the Excel book for which the table of contents is to be created.
Make instance visibleOFF
Open as ReadOnlyOFF

Set up Set variable Action to create %IndexSheetName%. Set the value of the variable to the name of the table of contents sheet.

Set up Set active Excel Worksheet Action.

Active worksheet withName
Worksheet name%IndexSheetName%

Set up Get all Excel worksheets Action.

Set up Set variable Action to create %Row%. Set the value of 1.

Set up For each.

Specify the %SheetNames% as the Value to be iterated.

Set up an If in For each.

First operand%CurrentItem%
OperatorNot equal to (<>)
Second operand%IndexSheetName%

Set up Write to Excel worksheet Action in If.

Value to write=HYPERLINK("#%CurrentItem%!A1","%CurrentItem%")
Write modeOn specified cell

Set up an Increase variable in For each to count up %Row% by 1.

Finally, set up Close Excel Action and save the file.

Executing this Flow will create a table of contents on the specified sheet.