How to use Write Cell activity to show number values rather than text values within Excel

excel
activities

#1

Hi,

I have a workflow grabbing 3 numbers from 3 different PDFs and inserting into an excel workbook using the write cell activity (see attached workflow).

My issue is that the excel workbook is now reading 2 of the 3 values as an excel “text” value, so the sum function within the worksheet is not working. For some reason, the 3rd value being passed to excel is being interpreted as an excel ‘number’ value.

I’ve tried removing all formatting in the workbook and re-adding the formatting before running the UiPath workflow, but that didn’t help. I’ve also tried converting to decimal, but the write cell activity requires a string input. Manually typing the numbers in the workbook works properly, even after the workflow has ran.

If I just type in a string variable that isn’t pulled from a PDF, it seems to work perfectly. For some reason, the fact that it pulls the string variable from a PDF seems to be the part that is screwing things up. Anyone have an idea?PDF to excel as values.zip (302.0 KB)


#2

Bump. I’ve tried this on other workflows, excel files, and PDFs and keep running into this issue.

I’ve found a workaround for this is to create a data table(s), convert any of the PDF strings to decimal type, add to the data table(s), and use the write range activity.

The workaround works, but is there any reason why the write cell won’t work directly? I’d prefer not to use workarounds when possible to reduce complexity


#3

Hi There,

I’m understanding that your problem is that it’s not formatting the value as a number when it outputs the value to the cell. Looking at your activities, it looks like you did it correctly, however, if there are spaces on the ends that would cause some problems. You can verify this with a WriteLine or Message Box; just place some characters on the ends of the variable, so it outputs like “aaa12345aaa” will show if you have no Newline or spaces on the ends. Also, if there is a $, that will need to be removed. If there are, then you just need .Trim when you assign the value.

If this also helps, you can use System.Double variables instead of String, and use Convert.ToDouble() or CDbl() when you make the assignment. (that will also tell you if it’s a number or not)

You might also consider using .Split() by the space and newline to find your value instead of substring character placement, which may or may not be more consistent and easier. You can also do .Split() by the word “Total” and take the last split if the value is the last value.

I didn’t run your workflow though, but those were my thoughts from briefly browsing it.

Regards.