Create a table of contents for sheet names in an Excel book.(Power Automate Desktop)
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
END
END
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
Flow creation steps
In the example, it is created on the Index sheet.

Set up Launch Excel Action.
Parameter | Value |
---|---|
Launch Excel | and open the following document |
Document path | The path of the Excel book for which the table of contents is to be created. |
Make instance visible | OFF |
Open as ReadOnly | OFF |

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.
Parameter | Value |
---|---|
Active worksheet with | Name |
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.
Parameter | Value |
---|---|
First operand | %CurrentItem% |
Operator | Not equal to (<>) |
Second operand | %IndexSheetName% |

Set up Write to Excel worksheet Action in If.

Parameter | Value |
---|---|
Value to write | =HYPERLINK("#%CurrentItem%!A1","%CurrentItem%") |
Write mode | On specified cell |
Column | 1 |
Row | %Row% |
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.

---
Discussion
New Comments
No comments yet. Be the first one!