Convert cell or range value from text to number [Excel][Vb]

@AS07 - You can studiox option in Studio…First use the “Use Excel file” and then format cells…

Note: To activate studiox activities first…choose filter in the Activities and select StudioX

Hi @AS07

since u are appending the datatable to excel ,

prior to that use the below code in invoke code with datatable argument dt1 as In/out

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(columnname)= CDbl(row(columnname).ToString)

Then append the data in excel file [ use excel application scope for this ]

Regards,
Nived N
Happy Automation

1 Like

Hi @prasath17 ,

I am using Uipath Studio Enterprise developer edition

Yes…check the above… @AS07

Hi Aditya,

I am reading one excel and I am appending that data in a Template excel, so reading only one excel and appending that data in the already existing sheet and data.

Hi @NIVED_NAMBIAR ,

Still facing the same error/issue.

@AS07 - Did you get a chance to check “Filter” in the activities to see if you StudioX options??

image

Hi @prasath17 ,

I am getting only this options

image

Hi @AS07 how u are writing data to.excel

Using workbook or excel activitiy ?

Regards

Nived N :robot:

Happy Automation :relaxed::relaxed::relaxed:

Hi @NIVED_NAMBIAR ,

Checked with both

@AS07 - Please check this…

Hi @prasath17 ,

I cant really use the Build Datatable or for loop , since it changes the data type or format of the data and lastly the data will be between thousands and lakhs (large data)

@AS07 - only If none of above suggested methods works out…you can use UiAutomation, and follow the below steps to fix it…

@AS07 - Here you go…

My Input

image

My Output After running the macro…

image

VBA macro i used…(Write it in a notepad and saveas .vbsfile…)

My workflow

Sub macro()
Range(“C:C”).Select 'specify the range which suits your purpose
With Selection
Selection.NumberFormat = “General”
.Value = .Value
End With
End Sub

image

3 Likes

Dear @prasath17,

Tried Your method , it gives me following error

P.S. I can’t change the security or excel settings on the rdp

@AS07 - you have to check the trust center setting on in the macro settings(in the excel) for this to work.

If you can’t the setting then I dont think this route will work . Please try the UiAutomation method or try Google it for any formula option.

@AS07 - how many rows in your excel file?

Hi @prasath17 ,

Currently its 93 thousand,seven hundred and twenty one data rows

This worked . Thank you…We can also write this code in .txt file and use invoke VBA Activity.

1 Like

it doesnot work. any way to convert to number in excel without using marco