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:
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