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.
| 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.
For those who want to learn Power Automate Desktop effectively
The information on this site is now available in an easy-to-read e-book format.
Or Kindle Unlimited (unlimited reading).
You willl discover how to about basic operations.
By the end of this book, you will be equipped with the knowledge you need to use Power Automate Desktop to streamline your workflow.