Change Excel column to Number format & how to remove warning in cells

Hi,

I am trying to apply Number format to excel column using vb script. But I am getting waring message in corner of the cell.
image

Regards,
Aarathy

hey Aarathy,
You can check this, he covered every scenario in his excel activities.

1 Like

Hi Sachin,

I tried using ‘Change Cell type’ activity of BalaReva.Excel activities. It changed the column format to number but when i click on the cell, it is showing warning that ‘Number Stored as Text’.

So please let me know how to fix this.

Aarathy

1 Like

OKay, if you can use VBA then there is a single line to set particular cell to set format
Range(“B2”).NumberFormat = “0.00”
You can set on all cells accordingly.

And this is Vb.net, check this
How to Format Excel Page in VB.NET

I have used the below script in Invoke Code activity.

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(PathOfFile, [ReadOnly]:=False)
excel.Visible = False
ws = CType(wb.Sheets(“Split_DB”), Microsoft.Office.Interop.Excel.Worksheet)
ws.Activate()
ws.Range(“D:D”).NumberFormat = “0.00”
wb.Save()
wb.Close()
excel.Quit()

After executing it, the column is showing as Number format but when i click on the cell, it shows warning that number is stored as text.

Aarathy

HI,
Can you please share the sample excel file with a sample work flow ?

Thank you
Balamurugan.S

can i use it but searching by column name instead range?

Yes you can, you can use Cells.find function to search your column name

Dim range_address As Range
Range("A1").Select    (So that find function starts searching from A1 cell)
Set range_address =Cells.Find(What:="ColumnName")
Range(range_address).NumberFormat = “0.00”

range_address.Column--> It will give you column No, so you can set the formatting on entire column as well.