I would preferably recommend reading the data into Excel first, then closing Excel, and then looping through files. This will prevent an Excel session from being left open in case the flow fails when renaming files. It will also be more efficient.
The Excel part should look like this:
It will get the end of the range dynamically and read it all into a data table. Then it will close Excel, but the data will be stored in the variable.
The renaming part will then look like this:
Note: For this to work, you need to have at least the same number of items (or more) in Excel as the number of files you have. If there are more files than rows in Excel, this will error out, so you might want to handle that.
Here's a code snippet you can copy and paste directly into PAD to create all those actions for you:
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\ohost\\OneDrive\\Desktop\\Test doc 1.xlsx''' Visible: False ReadOnly: True Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance
Folder.GetFiles Folder: $'''C:\\Users\\ohost\\OneDrive\\Desktop\\Test Folder''' FileFilter: $'''*''' 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 LoopIndex FROM 0 TO Files.Count - 1 STEP 1
File.RenameFiles.RenameAddText Files: Files[LoopIndex] TextToAdd: ExcelData[LoopIndex]['Account Name'] TextPosition: File.AddTextPosition.BeforeName IfFileExists: File.IfExists.DoNothing RenamedFiles=> RenamedFiles
END
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.
