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

Hello Guys,

I am trying to perform excel activities when I appending the input to excel, Its showing me error “Number saved in Text Format”, as shown in below image.

image

When I am trying to convert range format using below vb code the format is changed to custom or general but this error is still persisting.

Dim excel As Microsoft.Office.Interop.Excel.Application 'Init Excel application
Dim wb As Microsoft.Office.Interop.Excel.Workbook ’ Init Excel Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet 'Init Worksheet
Dim ob As Microsoft.Office.interop.Excel.ListObject 'Init ListofObject

Try
excel = New Microsoft.Office.Interop.Excel.ApplicationClass’create the instance of excel work book’
wb = excel.Workbooks.Open(“ExcelPath”)‘Open the excel the file’
excel.Visible=False
ws=CType(wb.Sheets(“Sheet1”),Microsoft.Office.Interop.Excel.Worksheet)‘select a sheet and activiates’
ws.Activate

		ws.Range("A1:A2").NumberFormat = "0.00"
		wb.Save
		excel.Quit

Catch es As Exception
System.Windows.MessageBox.Show(es.Message)
End Try

How to convert to Number in background without using any external activities or send hot keys or uiautomation.

@loginerror @NIVED_NAMBIAR @prasath17

Hi @AS07 !
I think you should work on the assign on that specific column before writing/appending range.
For instance, if you already have a loop in this specific datatable, using assign activity:
row(“nameofcolumn”) = myvalue (without the .tostring after it because this is what converts into a string)
If you did not put a .tostring, then maybe you should put CDbl(myvalue)
Would you mind sharing with us a screenshot on the assign for that specific column ?

Hello @Hiba_B ,

Unfortunately, I am not using any For loop or assign activity, I am reading an input excel and appending or writing in this excel directly as the number of rows are in thousands or sometimes lakhs.

I want to understand more … you are reading 2 excels into two different datatables and joining them isnt it?

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