Excel "Find and replace cells in Excel Worksheet" action is NOT working when it can not find text

(0) Share

Report

Posted on by DanielOlsson

111

Hello

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?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (10)

Answers (0)

MichaelAnnis 5,727 Moderator on at

Like (0)

Report

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”
Else
Replace text “potato” with “whatever”
End If
End
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!

Was this reply helpful? Yes No
DanielOlsson 111 on at

Like (0)

Report

Hello Michael
The whole point of this action is to avoid making lookup loops, it's a new feature in the latest release.

Was this reply helpful? Yes No
DanielOlsson 111 on at

Like (0)

Report

Hello Everyone

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.

Regards
Daniel

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

Is this a Beta action? I can't seem to find it...

Was this reply helpful? Yes No
DanielOlsson 111 on at

Like (0)

Report

Update to the latest version. it was in Aug release.

Was this reply helpful? Yes No
VJR 7,635 on at

Like (2)

Report

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.

Was this reply helpful? Yes No
DanielOlsson 111 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
sunilb Microsoft Employee on at

Like (0)

Report

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#findandreplaceaction_onerror

We need to go with the solution which is mentioned by @VJR

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report

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:

Was this reply helpful? Yes No