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.
Regards,
Aarathy
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.
Regards,
Aarathy
hey Aarathy,
You can check this, he covered every scenario in his excel activities.
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
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.