Why my Find and Replace did not work following file? Power Automate desktop

[dhana88]

Hello,

I am trying to find and replace • on my excel file using Power Automate desktop, but flow runs, and nothing will replace it. One more issue I will get an error when in close step. I am not sure where is going wrong. I attached some sample data, not sure whether the issue is on the data. 

https://docs.google.com/spreadsheets/d/1V5pIjJnMsi6b--R9bXUgpVn2WW5U-CBU/edit?usp=sharing&ouid=11599... 

Not sure if I can import my flow, can anybody please help me to fix this issue? 

Thank you so much 

[VJR]

Hi @dhana88 

The Find and Replace is working without any problems

Make sure you enable all matches.

Looks like you are trying to close a ".csv" as a ".xlsx"

Try the "Save Excel" action and give .xlsx in the document format.

[dhana88]

@VJR  Thank you so much for taking a look at it and for your advice. I really want to remove these two boxes after travel. Any ideas on how to do that? I would really appreciate it if you could let me know how to remove these 2 boxes. 

I want to remove the below characters, but I can give it on the MS Power Automate desktop. 
•



Thank you so much 

[Kaif_Siddique]

Hi @dhana88 ,

 is a Unicode character and Char Code is 5. I am not sure we can replace it using Find and replace cells in Excel worksheet action. But can be replaced by macro. Use below code and Run Excel macro using PAD.

Sub procReplace()
ActiveSheet.Cells.Replace what:=ChrW(5), replacement:="Replace Text", lookat:=xlPart
End Sub

If you like my response, please give it a Thumbs Up.
If this helps, please Accept as solution.

Regards

Kaif

[dhana88]

@Kaif_Siddique 

Thank you so much for the quick response. I tried so hard and different ways to replace , it but failed. I have a Power Automate online flow that will fail because of this .  So that is why I tried the desktop version. 

When it comes to macro, my hands are a little tight, because our IT people have blocked running a macro on SharePoint which is this excel file live. 

Do you know the way around how Ic and o this? Is there a way I can replace this character in Power Automate inline?  Online I tried, but almost every thing replacing character and rejoin did not work. 

Thank you so much 

[VJR]

Hi @dhana88 

A Substitute formula replaces both the types of characters.

Further now it depends on how you want to use this formula.

Either you can write it in another column in the same sheet or call the formula from a vbscript or VBA macro.

[dhana88]

@VJR 

Thank you for getting back to me. I like the substitute idea. Here is the situation, 

Every day I get a  CSV file, I am using a Power Automate desktop flow to open the csv file and save it as xlsx format. The reason I am converting this, is I am using that excel.xlsx file in Power Automate online to generate some templates, but now online flow fails due to  these characters. 

I can not save anything on the file, like formula or macro it will replace everything with new data every day. This is where I get stuck. That's why I used the Power automate desktop to replace it, but I can not use   up on that. 

[VJR]

Hi @dhana88 

You can call a macro or write the same into a vbscript that can run on any Excel file without writing any formulas onto that file.

[dhana88]

@VJR 

thank you so much for your continuous help. Can you please bear with me tell how to implement this? I know how to run macro, the part that i don’t understand is how to implement it. 

so basically 

i have to Launch CSV

Then save as excel 

Again launch excel (which was above one) 

Run Macros ( this where I get confused) 

Save and closes the file. 

I can not have the macro in excel

file due to resting everyday. Do I have to use another excel file with macro? 

sorry I am new to this.

Thank you so much 🙏🙏🙏

[VJR · Accepted solution]

• Lets say you have finished saving your CSV as XLSX. Lets call this file as Main.xlsx (no worries even this name changes everyday)
•  Take a new Excel file. Lets call this as MacroFile.xlsm. Write a macro in this file where the macro function will accept the file path and name of any other excel file.

sub MyMacro(filepathwithname)

' via macro code open the filepathwithname

'replace the unwanted characters

'save and close the file

End sub

You will get tons of VBA code on the internet on how to open another excel file from within a macro.

Also will get how to replace text.

That's it.

• How to run:

From your PAD, call the MacroFile.xlsm and pass the path and name of the Main.xlsx file

So basically you will always have one macro file at some location which will be known to PAD, which will be calling to open any other external Excel file to do the Find, Replace, Save and Close.