topic Excel in Product Forum

Excel

Swetha__Devaram — Fri, 12 Jul 2019 20:33:00 GMT

Hi,

I have some data in an excel file which have values like "12345,67889" i.e. numbers with each 5 digits separated by coma. When I import data to collection and export to excel, the value in the excel file are changing to "1,234,567,889". Is there a way I can keep the formatting the same way as "12345,67889" ?

When I check inside the collection they appear to be fine as "12345,67889".

Please advice.

Thanks in advance.

------------------------------
Swetha Devaram
RPA Developer
Health Markets
America/Chicago
------------------------------

RE: Excel

GopalBhaire — Sat, 13 Jul 2019 06:09:00 GMT

This is because Excel is parsing entered input as number

Edit the Format Cell Action of Excel VBO to :

GetWorkbook(handle,Nothing).ActiveSheet.Range(cellref).NumberFormat = format

Just converted the (cellref,cellref) to (cellref) so we can work on range and not a single cell.
Pass cellref as A:B or whichever column your data will be going into and format as '@' (without quotes) in respective data items

This will convert column A and B into 'Text' columns in excel, then you can write data to it using write collection and excel won't convert your data

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: Excel

Swetha__Devaram — Tue, 16 Jul 2019 13:51:00 GMT

Thank you so much Gopal.

------------------------------
Swetha Devaram
RPA Developer
Health Markets
America/Chicago
------------------------------

RE: Excel

maneesh.vemula1 — Thu, 03 Oct 2019 14:45:00 GMT

Hi - I am facing a similar issue as well. In my case I am copying elements from the screen using Global send keys (ctrl+c), opening the workbook and pasting the data using Paste(Basic) of MS Excel VBO. In this process the numbers such as 6731900236050 are being converted to 6731900000000. When i try to retrieve the values from the file, i am getting 6.73E+12. I tried format cell like you suggested before pasting the data into the workbook but that did not help. Do you have any suggestions for this particular issue?

------------------------------
Maneesh Vemula
RPA Specialist
America/New_York
------------------------------

RE: Excel

SandeepNalla — Fri, 25 Oct 2019 20:58:00 GMT

Format the column to type "Text" before pasting the data. It should work.

------------------------------
Sandeep Nalla
Senior Consultant
Lateetud Inc
America/New_York
------------------------------