Set the number format of cells in an Excel file

Use the NumberFormat Excel property

This solution applies to external Excel files, not to the QuickTest Professional data table.

Excel's object methods are not part of QuickTest Professional, therefore, they are not guaranteed to work and are not supported by Mercury Technical Support. Any changes that Microsoft may make to these methods are not the responsibility of Mercury.

Example:
filename = "C:\temp\sample.xls"

Set ExcelObj = CreateObject("Excel.Application")
ExcelObj.Workbooks.Open filename
ExcelObj.Visible = True
Set NewSheet = ExcelObj.Sheets.Item(1)

' Format the Cells to General
'NewSheet.Columns("A").Select
NewSheet.Cells(1,1) = "test"
NewSheet.Cells(1,1).Select
ExcelObj.Selection.NumberFormat = "General"

' Format the cells to Percentage
NewSheet.Cells(1,2) = ".50"
NewSheet.Columns("B").Select
ExcelObj.Selection.NumberFormat = "##%"

' Format the cells to Currency
NewSheet.Cells(1,3) = "6.50"
NewSheet.Columns("C").Select
ExcelObj.Selection.NumberFormat = "$0.00"


ExcelObj.ActiveWorkbook.Save
ExcelObj.Application.Quit
Set ExcelObj = Nothing