Descriptive Programming to retreive data from an Excel sheet

I’ve an excel sheet which consisting of Test results. The contents of Excel sheet are Test case id, Test case  description,  status and comments. In status there is pass and fail criteria of test case mentioned. I want to retrieve number of failed test cases available in the excel sheet. Then I can write my VB scripting in this way. First I’ve to create an object to retrieve the data from an excel sheet. I use dynamic descriptive programming and create an object and then I retrieve the data in this way

Step1. Set Mycom = creatobject (“ADODB.connection”)

Step2. Mycom.open “driver = (Microsoft excel (*.xls)); OBQ= Path ‘, Randomly= true”

‘I’m creating a connection between the tool and the excel sheet firs . I’m giving path of the excel sheet

Step3. Set Myrs = createoject(“ ADODB. Recordset”)

Step4. Myrs.open “select count(*) from [Test Result] where status = ‘ fail’ ”, Mycom

‘ I’m might have many tabs in excel sheet so I want to specify a particular tab named Test Result , Remember when you specify the Tab name be specific at cases because it is case sensitive and any changes may fail the scripting which we have written.

Step5. Msgbox myrs(0)

‘Release the objects

Step6. Set Myrs = nothing

Step7. Set Mycom = nothing

And when we run the script the number of failed criteria of the test result tab will be displayed in in a msg box.  If we count manually the failed test cases are 8. So the result will be 8 displayed in the Msgbox.

Similarly if I want to retrieve the test cases, test case description and comments columns  for each failed test case in the Excel sheet then I can write my script in this way

Step1. Set Mycom = creatobject (“ADODB.connection”)

Step2. Mycom.open “driver = (Microsoft excel (*.xls)); OBQ= Path ‘, Randomly= true”

Step3. Set Myrs = createoject(“ ADODB. Recordset”)

Step4. Myrs.open “select  Testcase_id,Test case Description, Comments,  from [Test Result] where status = ‘Fail’ “,Mycom

‘I can either give the Column name or if not I can give the column number as 1, 2 or so on. I use the Do while loop here to get the data till the end of the file

Step5. Do while not Myrs.EOF                    (end of File)

Step6. Msgbox Myrs ( “ Test case_ID “)

Step7. Msgbox Myrs(“ Test case Description”)

Step8. Msgbox Myrs( “Comments”)

Myrs. Move Next

Loop

‘Release the object

Step9. Set Myrs = nothing

Step10. Set Mycom = nothing

If we want to concatenate the same data in the form of List instead of Retreiving  each column in a msgbox then I use ampersand symbol and get them in a single throw 

.

Step6. Msgbox Myrs(“ Test case Description”) & “:” & (“ Test case_ ID “) &”:” & (“ Comments”)

And when we analyze the result data, The testcase_id, description and comments will be displayed in the msgbox and we have to click ok to close the msgbox and get the result of another fail criteria row.

If we are not comfortable in clicking Ok all the time then we can use print statement in front of Msgbox so that the data of all the fail criteria’s available in the test result sheet will be displayed in the form a list.

So this how you write Scripting in QTP to extract data from an excel sheet.