Changing excel cell format with UiPath

Help
1

I’m looking for a way to use uipath to change a cell’s format from general to number. I’m passing my values in as doubles but when I write them to excel, the values that should be showing up as 0 are showing as things like “-4.55E-13” and “1.36E-12”. When I manually change the cell format to “number” the values change to 0 but I need a way for my program to automatically change the cell formats for the values to show up correctly.

I’ve looked at all the other posts but can’t find something that I think would work for me. Any ideas??

2

It sounds silly, but try switching to the opposite of the write range activity you’re using. So if you’re currently using an Excel scope with the excel write range activity, try instead using the workbook write range and vice versa.

3

I was originally using write range inside of an excel scope so that I could create a new workbook to write the values to. I tried using the create file activity to create the new workbook before writing to it without excel scope but now I got the error “Archive file cannot be size 0”. Do you know how to get around this?

4

@caitlinscott - no need to use the create file activity. If you use the workbook write range and the file you specify doesn’t exist, it will create one for you

1 Like
5

@Dave okay I got rid of the create file activity but unfortunately it didn’t fix the issue. It’s actually a bit worse to be honest - instead of just showing a number like “-4.55E-13” it’s showing numbers like “9.09494701772928E-13”

Any other ideas?

6

I made sure that every instance of the variable where I was manipulating it had been set to “convert.ToDouble(variable)” and this fixed a lot of the values that should’ve been zeros, but for some reason I still have some of them showing up with “4.55E-13”

7

Try using a decimal variable instead of double. If you’re dealing with monetary values, you should always be using a decimal anyways, but decimal provides more precision than a double variable

8

I don’t know if that would help because I feel like it would still be showing up as a very large decimal as opposed to 0 wouldn’t it?

I ended up adding an invoke vba activity to change the cell number format to “0.00” and it seemed to fix the issue! The values are all displaying as 0.00 now instead of the weird decimal things.

Thank you for your help though!

1 Like
9

Hi,
To change cell format , you can use the “ChangeCellType” activity which is existing in the blow link.

Regards
Balamurugan.S

4 Likes
10

Hi ,

I have this package but it done not have any activity . But it does not have any activity called “ChangeCellType”.

How should i use this activity.

Thanks,
Jelin

image

image

1 Like
11

Hi @jelin,
Check it here and use the latest version 2019.4.0…

image
image605×693 60.3 KB

Regards
Balamurugan

1 Like
12

Thanks… i Got it :slight_smile:

2 Likes
13

Thank you very much for your packages!

Your contribution has saved me a lot of time in my projects. :smile:

I wish you the very best and have a nice day :smile:

1 Like
14

Hi @ruh_hastasi77,
Thank you . Your wishes and comments make me to do more things…

Balamurugan.S

4 Likes
15

@balupad14 For me it is not working any idea?Please help me.

2 Likes
16

@balupad14 Got it thanks man it is doing wonder, If you are okay can you please share me the source code of that custom package

1 Like
17

Thank you @arunshiva

18

Are you have a example or screenshot for how to add value in the Properties ribbon the Change Cell Type Activity own?

19

I suggest you change the cell format by using VBNET code

20

HI @Brian_Henokh1,

May I know Why you want to use the properties which in Ribbon.

image
image277×702 14.2 KB

The format Cells has all the Properties which is in the above one

image
image676×628 9.12 KB

Regards
Balamurugan.S