Search in Excel / DataTable

[idolast]

Hi again,

I would like to use QTP to verify whether my application has worked correctly with the data I putted in. Therefore I thought to compare the resulting data with the one, I say it has to be right.
The easiest way to do this would be to compare an Excel-Sheet generated by the application, with the Excel-Sheet containing data I defined.

Now I only want to compare one line in the resulting Excel-Sheet with my Excel Sheet. To find this row I need to search in one column of the sheet for a certain value. In Excel this would be no problem. But with QTP I do not know how to implement this.

I thought it would be good to import both Excel-Sheets into my QTP-test, search for the row and then compare each value in that row the ones of the other sheet.

This works so far. But I did not find a way to really search for a value in the sheet within the DataTable in QTP. For now I did it with a for-statement, iterating through the rows... but this is quite slow...

Is there a function in QTP to search within the DataTable? Or is there a better way to do what I like to do?

Thanks in advance.

[vramu1]

Hi,
All seniors please give your comments, If I'm doing any thing wrong.

I'm also new to QTP and using the same Data Driven frame work using Excel work sheets. It is working great for me.
What i'm doing is, I'm using two work sheets (one for Expected values and other is for Actual results and comparison) instead of two work books so, I can mention only one excel path in my test.
the code I used is,

Code:

strExcelPath = "C:\Automation\QTP_ResultsNReport.xls"
Set  objWorkbook = objExcel.Workbooks.Open (strExcelPath)
' reading values from Expected Values worksheet
set ExpectValues = objExcel.ActiveWorkbook.Worksheets("ExpectedValues")
ExpctValue1 =  ExpectValues.cells(1, 1)
ExpctValue2 =  ExpectValues.cells(1, 2)
ExpctValue3 =  ExpectValues.cells(1, 3)
' reading values from Actual Values worksheet
set ActValues = objExcel.ActiveWorkbook.Worksheets("ActualValuesNResults")
ActValues1 =  ActValues.cells(1, 1)
ActValues2 =  ActValues.cells(1, 2)
ActValues3 =  ActValues.cells(1, 3)
' Now compare both Expected and Actual values
if ExpctValue1 = ActValues1 then
     if ExpctValue2 = ActValues2 then
        if ExpctValue3 = ActValues3 then
            TestStatus = "Pass"
        end if
     end if
else
    TestStatus = "Fail"
End if

Hope this make sense to your question. let me know.
You can put lots of more masala code into this methodology.
View the attached spreadsheet image. it will give you a rough idea what I'm trying to do.

Ram.

[idolast]

Hi vramu1,

Thank you very much for your answer.

When I tried to understand your approach, I realized that you can really integrate Excel into QTP, like in programming languages like VB or C#, with the CreateObject-method.

The only catch is that within QTP the actual methods and properties of the object instantiated with CreateObject (in this case the Excel, Workbook, Sheet, ..-objects) are not recognized. So you really have to know the methods you like to call.

I helped myself with opening Visual Studio and creating VB-code doing exactly my task and copying this code into the Expert View of QTP.

Now the search for the specific row works very fast.
Thank you.

And here is my code, how I did it:

Code:

referenceNumber = DataTable("referenceNumber", dtLocalSheet)

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open(DataTable("Filename", dtGlobalSheet))

Set actualValues  = objWorkbook.Worksheets(DataTable("Sheetname", dtGlobalSheet))

foundRow = actualValues.Columns(2).Find(referenceNumber).Row

countCols = actualValues.UsedRange.Columns.Count
countRows = actualValues.UsedRange.Rows.Count

If foundRow > countRows Then
    Reporter.ReportEvent micFail, "Item " & referenceNumber & " not found in Report", ""
else
   Reporter.ReportEvent micPass, "Item " & referenceNumber & " found in Report", "Item " & referenceNumber & " found in Report in line " & foundRow
End If

[newqtp]

Hi Ram,
I'm also new to QTP I've one question how do you save your test result in excel sheet.Can you please explain it.
Thanks.

[vramu1]

I'm writing test results in excel after comparing the values with in the code.
for example,

Code:

set ActValues = objExcel.ActiveWorkbook.Worksheets("ActualValuesNResults")
ActValues1 = ActValues.cells(1, 1)
ActValues2 = ActValues.cells(1, 2)
if ExpctValue1 = ActValues1 then
if ExpctValue2 = ActValues2 then
TestStatus = "Pass"
end if
else
TestStatus = "Fail"
End if
'writing test results in cell A3 in the "ActualValuesNResults" work sheet
ActValues.cells(1, 3) = TestStatus

hope this answers your question.

Cheers
Ram