Thursday, June 4, 2009

Working with excel files in QTP using "Excel.Application" class?

Set xlApp = CreateObject("Excel.Application")
Set xlWorkBook = xlApp.workbooks.add 'or Set xlbook=xlapp.activeworkbook
Set xlWorkSheet = xlWorkbook.worksheet.add 'or Set worksheet=xlbook.sheets(3)
xlWorkSheet.Range("A1:B10").interior.colorindex = 34 'Change the color of the cells
xlWorkSheet.Range("A1:A10").value = "text" 'Will set values of all 10 rows to "text"
xlWorkSheet.Cells(1,1).value = "Text" 'Will set the value of first row and first col
rowsCount = xlWorkSheet.Evaluate("COUNTA(A:A)") 'Will count the # of rows which have non blank value in the column A
colsCount = xlWorkSheet.Evaluate("COUNTA(1:1)") 'Will count the # of non blank columns in 1st row
xlWorkbook.SaveAs "C:\Test.xls"
xlWorkBook.Close
Set xlWorkSheet = Nothing
Set xlWorkBook = Nothing
Set xlApp = Nothing

1 comment: