Power Platform Community Forum Thread Details

i want to rename first worksheet in multiple workbooks using power automate desktop
i am using the below steps to do it

Open Power Automate Desktop and create a new flow.
Add the "Get Files in Folder" function to the canvas and specify the folder containing the workbooks.
Use a loop to iterate through each file in the list returned by the "Get Files in Folder" function.
Inside the loop, add the "Launch Excel" function and set the file path to the current file from the loop variable.
Add the "Rename Excel Worksheet" function and set the following properties:
- Excel Instance: Use the output from the "Launch Excel" function.
- Worksheet Name: Set it to "data" (or whatever name you want to rename the worksheet to).
- Worksheet Index: Set it to 1 (as the first worksheet has an index of 1).
Add the "Close Excel" function to close the opened file.
Save and run the flow. This will loop through each workbook in the specified folder, rename the first worksheet to "data", and close the file.

However, while following the steps, I encountered an issue where the flow stops automatically and throws an error on some files. Upon checking the pattern, I observed that the files throwing the error are those with a hyperlink as the name of the worksheet.
I was hoping you could assist me in troubleshooting this issue.

Categories:

I do hope you know that an error will occur even if you rename the worksheet manually which has invalid characters:

So if I had to rename a worksheet with a hyperlink (just taking an example), it won't. Because it contains invalid characters and an error message will pop-up like this:

But incase your input variable has these warning conditions, we can do a check before entering this name.

See below flow:

Configuration of some actions:

Code (just copy-paste the code into your flow):

IF (Contains(NewNameExcelWorksheet, ':', True) OR Contains(NewNameExcelWorksheet, '/', True) OR Contains(NewNameExcelWorksheet, '\\', True) OR Contains(NewNameExcelWorksheet, '?', True) OR Contains(NewNameExcelWorksheet, '*', True) OR Contains(NewNameExcelWorksheet, '[', True) OR Contains(NewNameExcelWorksheet, ']', True)) = $'''True''' THEN
 Text.Replace Text: NewNameExcelWorksheet TextToFind: $''':''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> NewNameExcelWorksheet
 Text.Replace Text: NewNameExcelWorksheet TextToFind: $'''/''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> NewNameExcelWorksheet
 Text.Replace Text: NewNameExcelWorksheet TextToFind: $'''\\''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> NewNameExcelWorksheet
 Text.Replace Text: NewNameExcelWorksheet TextToFind: $'''?''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> NewNameExcelWorksheet
 Text.Replace Text: NewNameExcelWorksheet TextToFind: $'''*''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> NewNameExcelWorksheet
 Text.Replace Text: NewNameExcelWorksheet TextToFind: $'''[''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> NewNameExcelWorksheet
 Text.Replace Text: NewNameExcelWorksheet TextToFind: $''']''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> NewNameExcelWorksheet
END
IF NewNameExcelWorksheet.Length > 31 THEN
 Text.GetSubtext.GetSubtextFromStartTo Text: NewNameExcelWorksheet NumberOfChars: 31 Subtext=> NewNameExcelWorksheet
END
IF IsEmpty(NewNameExcelWorksheet) THEN
 EXIT Code: 0 ErrorMessage: $'''The New Name for the Excel Worksheet is empty. Make sure its not empty for renaming purpose.'''
END
Folder.GetFiles Folder: $'''C:\\Users\\UserName\\Desktop\\Testing excel''' FileFilter: $'''*.xlsx''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
LOOP FOREACH CurrentItem IN Files
 Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItem Visible: True ReadOnly: False Instance=> ExcelInstance
 Excel.RenameWorksheet.RenameWorksheetWithIndex Instance: ExcelInstance Index: 1 NewName: NewNameExcelWorksheet
 Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
END

The 3 conditions I have put in place like this:

1) Replace the text which contains those invalid characters to empty. (Using this: %''%)

2) Make sure it is not exceeding 31 characters, if yes, choose only the first 31 characters of your new text.

3) You have to make sure its not empty, if yes, stop the flow because you cannot rename a worksheet with empty value. You can also log this into a file saying, "The New Name for the Excel Worksheet is empty. Make sure its not empty for renaming purpose."

I hope this helps.