cancel
Showing results for 
Search instead for 
Did you mean: 

Excel

Swetha__Devaram
Level 4
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
------------------------------
4 REPLIES 4

GopalBhaire
Level 10

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

Thank you so much Gopal.

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

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

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

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