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