cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DanielOlsson
Helper II
Helper II

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

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?  

 

DanielOlsson_0-1631002073442.png

 

10 REPLIES 10
MichaelAnnis
Memorable Member
Memorable Member

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!

 

 

Hello Michael 

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

DanielOlsson
Helper II
Helper II

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

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.

MichaelAnnis
Memorable Member
Memorable Member

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

MichaelAnnis_0-1631033474340.png

 

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

VJR
Memorable Member
Memorable Member

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.

 

VJR_0-1631074920840.png

 

When text is found returns non-zero values.

VJR_1-1631075035784.png

 

 

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?

sunilb
Microsoft
Microsoft

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 

 

 

VJR
Memorable Member
Memorable Member

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.

VJR_0-1631093473698.png

 

If something is found then Rows will have a non-zero value.

Meaning: the Cells Datatable has 1 Row in the below case.

VJR_1-1631093526505.png

 

So checking the value of Rows will suffice.

 

Rows value of Cells Datatable: %Cells.RowsCount%

 

After displaying the above message:

VJR_0-1631094361624.png

 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,058)