I have a problem with the new Find and replace cells in Excel Worksheet" action.
I am using it to find a word, it works brilliantly when you find a word that exists but when it fails to find I expect it to let me know and not carry on as nothing happened. Below is a screenshot of a very basic flow where we look for a word potato that does not exist in the file. As it isn't finding it should go to a label and set a variable but it is not doing any of these and just carries on without either logging a fault or performing the actions on not fund.
Does anyone else experience this?
I haven’t used this feature, so I am just going to say what I think is happening and how to work around that.
The error “failed to find and replace text” does not mean the text didn’t exist. I believe it means that the process literally failed (ex. Your Excel Instance was closed).
So, I would rewrite this to:
Read from Excel (Range) to %ExcelData%
For each %CurrentItem% in %ExcelData%
If %CurrentItem% does not contain “potato”
Change %CurrentItem% to “Not found”
Replace text “potato” with “whatever”
Write %ExcelData% to (Range)
This extracts it all into a DataTable, rewrites it in the DataTable, then writes the DataTable back to Excel.
Best of luck!
Support just called me and they have agreed that there is a potential defect in the response from this new action. When the action do not find what its asked to look for, it should execute the instruction set and it does not hence its been forwarded back to developers. In case you have the same experience.
Agreed. However, my gut is telling me that the text not existing in “find and replace” is not an error, so you won’t be able to get there with the error module.
I can toy around with it in about 3 hours and see if there is a better solution, but I am suspecting that to say “if text does not contain ‘potato’, change to Not Found” will have to be done another way.
Hello @DanielOlsson ,
You could think of taking the next desired course of action based on the FoundColumnIndex and FoundRowIndex output variables.
If not found, both return 0.
When text is found returns non-zero values.
Hello VJR, that is actually a good workaround as locations remain 0 0 as it does not find. A simple IF statement checking the output.
But if we look for "all", the variable produced changes to %Cells% and that remains totally empty with no zero. As well a workaround but if in a loop, there is no way to clean a variable to be empty and it will contain old values as it does the 2nd search?
Above action will through error only when it has problem in finding and/or replacing the specified text.
Kindly refer the below article. https://docs.microsoft.com/en-us/power-automate/desktop-flows/actions-reference/excel#findandreplace...
We need to go with the solution which is mentioned by @VJR
Hi @DanielOlsson ,
I understood what you are trying to convey and so tried the same.
When 'All' is selected and if there are no matches found, then the Rows will always show 0.
Meaning: the Cells Datatable has 0 Rows in the below case.
If something is found then Rows will have a non-zero value.
Meaning: the Cells Datatable has 1 Row in the below case.
So checking the value of Rows will suffice.
Rows value of Cells Datatable: %Cells.RowsCount%
After displaying the above message:
Learn how to create your own user groups today!
Check out the new Power Platform Community Connections gallery!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.