cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasFonn
Resolver I
Resolver I

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

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 "ThomasFonn_0-1663674523188.png"-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"?

1 ACCEPTED SOLUTION

Accepted Solutions
Nived_Nambiar
Resolver III
Resolver III

Hi @ThomasFonn 

 

I think using macros can help to acheive this

 

Try that way

 

Thanks & Regards,

Nived N

View solution in original post

6 REPLIES 6
Nived_Nambiar
Resolver III
Resolver III

Hi @ThomasFonn 

 

I think using macros can help to acheive this

 

Try that way

 

Thanks & Regards,

Nived N

VJR
Super User
Super User

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"?

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 
ValueFormatValueFormat
2,059General2 059Number
2,9875General29 875Number
1,0056General10 056Number
1,0303General10 303Number
2,9108General29 108Number
330,089General330 089Number
89,488General89 488Number
189,839General189 839Number
90,236General90 236Number
101,949General101 949Number
4,009General4 009Number

 

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 🙂

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.

 

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.

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!

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,124)