The below function exports the data to an excel sheet from QTP. We can use this function to export the test results to excel. The function would be called every time a custom checkpoint occurs.
1: Function WriteData(Flag,ResultArray,i)
2: 'Output data from result array to excel
3: ExcelPath="FilePath.xls"
4: WorkSheetName2="Sheet1"
5: StatusPass="Pass"
6: StatusFail="Fail"
7: StatusNoRun="No Run"
8: 'Modify Array with status
9: If Flag=1 Then
10: ReDim Preserve ResultArray(i)
11: ResultArray(2)=StatusPass
12: else
13: ReDim Preserve ResultArray(i)
14: ResultArray(2)=StatusFail
15: End If
16: 'Ensure that the xls file exists
17: CheckFile (ExcelPath)
18: 'Create Excel Object
19: Set ObjExcel=CreateObject("Excel.Application")
20: Set ObjWorkbook=ObjExcel.Workbooks.Open(ExcelPath)
21: Set ObjSheet=ObjWorkbook.Worksheets(WorkSheetName2)
22: 'Count number of used rows
23: iRowCount = ObjSheet.usedrange.rows.count
24: i=iRowCount+1
25: j=0
26: For each k in ResultArray
27: j=j+1
28: ObjSheet.cells(i,j).value=k
29: Next
30: 'Get the final value of status field
31: Status=ObjSheet.cells(i,3)
32: 'Mark the colour of status field depending on its value
33: Select Case Status
34: Case StatusPass
35: ObjSheet.cells(i,3).interior.colorindex =4
36: Case StatusFail
37: ObjSheet.cells(i,3).interior.colorindex =3
38: Case StatusNoRun
39: ObjSheet.cells(i,3).interior.colorindex =6
40: End Select
41: 'Save workbook
42: ObjWorkbook.Save
43: 'Close excel sheet
44: ObjExcel.DisplayAlerts = False
45: ObjExcel.Quit
46: ' destroy the other objects
47: Set ObjExcel = Nothing
48: Set ObjSheet = Nothing
49: Set objFS = Nothing
50: End Function
Line Number wise Explanation:-
Flag is a Boolean variable where 1 is a pass.
ResultArray stores the strings for pass/fail status for each checkpoint.
i corresponds to number of strings in the Result array.
The excel sheet looks like:-
1: We pass the variable Flag,ResultArray,i as function arguments.
3: Specify the path name in argument “ExcelPath”. Here we can also export the path name to a XML file by using the below code.
Environment.LoadFromFile(“pathEnvFile.xml”)
ExcelPath=Environment.Value(“OUTPUTEXCELPATH”)
we load the environment variables from the XML file. The contents of xml file can be as below
1: <Environment>
2: <Variable>
3: <Name>FLOWREPOSITRY</Name>
4: <Value>pathShared_Repository.tsr</Value>
5: </Variable>
6: <Variable>
7: <Name>FUNCTIONLIBRARY</Name>
8: <Value>PathGeneralFunctionLibrary.qfl</Value>
9: </Variable>
10: <Variable>
11: <Name>INPUTEXCELPATH</Name>
12: <Value>PathTestData.xls</Value>
13: </Variable>
14: <Variable>
15: <Name>OUTPUTEXCELPATH</Name>
16: <Value>PathTestResult.xls</Value>
17: </Variable>
18: <Variable>
19: <Name>RECOVERYSCENARIO</Name>
20: <Value></Value>
21: </Variable>
22: </Environment>
4: Define the variable to store name of worksheet.
5: StatusPass is the string to contain “Pass” string
6: StatusFail is the string to contain “Fail” string
7: StatusNoRun is the string to contain “No Run” string
9: If Flag=1, then Store Pass status to 2nd column of ResultArray other wise Store Fail status to 2nd column of ResultArray
17: Check if the output file exists.
19: Create the object for Excel
20: Create the object for workbook
21: Create the objects for sheet.
23: Count the number of used rows in output sheet(so that the next run would be stored in next line) and store in variable iRowCount.
24: Set value if i to the next empty row number
15: Count the number of columns in input sheet and store in variable iColumnCount.
18: for each row, 1st column value is saved in the variable TestName
20: The controls goes into If condition if the TestName retrieved is same as TestId passed. Other wise it is skipped and goes to next row till end of rows.
21: The inner for loop traverses through the columns of required test.
23: Each column value is saved in DataArray.
26: The for loop traverses Result Array and places it’s data in the excelsheet
31: Retrieve the status of test from ith row, 3rd column.
33. The case statement is used to colour the cells according to their status. Red for Fail, Green for Pass and
42: Save the excel sheet
44: We need to close the excel object.
47: We should destroy the Objects as not doing so would
Hi Amita
What arguments do i pass from the main test for WriteDate(Flag,ResultsArray,i)
Flag is a Boolean variable where 1 is a pass.
ResultArray stores the strings for pass/fail status for each checkpoint.
i corresponds to number of strings in the Result array.
Please see the "Line Number wise Explanation" section in the post for details