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.
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
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 ?
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 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.
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)
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