Reading and re-writing values removes commas in numbers in Power Automate Desktop

[ThomasFonn]

This approach removes commas in some numbers for some reason. (EDIT: This may be a culture issue with how comma/dot separation is approached under the hood in the Excel-API.)
Manually copying and pasting the same values with the ""-alternative works and the commas remain, but i'd prefer not to add too much UI-automation to my flow.

Is there a way to paste values into Excel in Power Automate Desktop the same way as the you do with the Excel "xlPasteValues"?

[Nived_Nambiar · Accepted solution]

Hi @ThomasFonn 

I think using macros can help to acheive this

Try that way

Thanks & Regards,

Nived N

[VJR]

Hi @ThomasFonn 

vbscript might be a good approach without UI automation.

I haven't got full clarity of your requirements. What is your input and what output are you looking for? 

Have you tried using the "Get cell content as text" advanced option of "Read from Excel worksheet"?

[ThomasFonn]

Hi @VJR

My input is made by a SQL-query and the values are correct from the SQL if I look in the Excelfile, but the numbers aren't recognized as numbers for the =SUM()-function unless I do the paste special.

If I try adding a "read from excel" and then "write to excel" which simulates this, I end up with the following output

Input		Output
Value	Format	Value	Format
2,059	General	2 059	Number
2,9875	General	29 875	Number
1,0056	General	10 056	Number
1,0303	General	10 303	Number
2,9108	General	29 108	Number
330,089	General	330 089	Number
89,488	General	89 488	Number
189,839	General	189 839	Number
90,236	General	90 236	Number
101,949	General	101 949	Number
4,009	General	4 009	Number

However, my desired output should be the same as the input

I just want to do a Paste Special -> Paste Values, as some of the formulas in another sheet won't sum these cells as it doesn't see them as a number until i do so.

The "Get cell content as text" doesn't seem to make a difference in this case

I think i'll go with the VB-script solution in the end, but I wish there was a better way 🙂

[VJR]

Okay if you think it would do you a lot of saving then you can have the SQL itself return the sum of the values either in the same SQL query you are already running or by firing a new one.

Good luck.

[ThomasFonn]

Hi @Nived_Nambiar 

It worked the way you suggested and it was easy to implement.
I hope in the future that paste special gets implemented into the advanced paste options in Power Automate Desktop though.

[VJR]

Hi @ThomasFonn ,

I would still say vbscript as the easiest option because it wont make use of an Excel file to have a macro, and will make direct changes to your Excel file.

Something like this..

Dim objXLApp, objXLWb, objXLWs

Set objXLApp = CreateObject("Excel.Application")

objXLApp.Visible = True

Set objXLWb = objXLApp.Workbooks.Open("C:\Test\sample excel.xlsx")

'~~> Working with TestSheet
Set objXLWs = objXLWb.Sheets("TestSheet")


'For entire column
'objXLWs.Columns("A:A").NumberFormat = "0.00"


objXLWb.save()

objXLApp.Quit

Above code is taken from one of my another posts which is to change the format of an entire column using vbscript.

Without reading and writing the Excel back you can directly change the format of the column to the desired Number formatting or add the copypaste approach if you have to.

Either ways, if you choose to go by this approach I am happy to help with the script.

Good day!