+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: UFT / QTP Beginners (https://www.learnqtp.com/forums/Forum-UFT-QTP-Beginners)
+--- Thread: Compare a link name to a column in Excel (/Thread-Compare-a-link-name-to-a-column-in-Excel)
Compare a link name to a column in Excel - mv8167 - 01-20-2012
I have a set of link names that I have copied into column D of one Excel spreadsheet.
If the column D of #1 Excel spreadsheet matches any of the values in column E of #2 Excel spreadsheet, the link name is Ok to be used.
How is the search of an entire column possible? Must I know how many rows there are?
RE: Compare a link name to a column in Excel - Ankesh - 01-20-2012
Yes u must first get the row count. You can use usedrange. once you get the number of rows, you can proceed with the comparision.
RE: Compare a link name to a column in Excel - mv8167 - 01-20-2012
Ankesh,
Ok, greast but...
How do I do the compaireson of a link name to an entire coulmn of data?
thank you ... ;-)
RE: Compare a link name to a column in Excel - Ankesh - 01-23-2012
Lorena,
Yes comaparing a link with entire coulmn seems to be tricky and time consuming as well. One idea would be to use the excel Find property to check for the value which we are looking for.
I have developed the below code which worked fine for me. Can you try and check on your side if this solves your purpose?
'***********************************************************
strSearchedItem ="91773978"'please specify the link that you want to search
Code:
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\TestData_SAP_FICO_FI_VF11_CANCEL BILL DOC_037.xls") 'Enter the file path
Set actualValues = objWorkbook.Worksheets("FICO_CANCEL_BILL_DOC") 'Enter the sheet name
objExcel.Visible=True '
intFoundRow = actualValues.Columns(4).Find(strSearchedItem).Row ' Find the searched item in excel on the column specified
intRowCount = actualValues.UsedRange.Rows.Count 'Get the total row count
If intFoundRow > intRowCount Then
Reporter.ReportEvent micFail, "Item " &strSearchedItem & " not found in Report", ""
else
Reporter.ReportEvent micPass, "Item " &strSearchedItem & " found in Report", "Item " & strSearchedItem & " found in Report in line " & intFoundRow
End If
'Release the objects
objExcel.Visible=False
Set actualValues=Nothing
Set objWorkbook=Nothing
objExcel.Quit
Set objExcel=Nothing'*********************************************************
Regards,
Ankesh
RE: Compare a link name to a column in Excel - mv8167 - 01-23-2012
Wow! Ankesh, thank you.
I will try this and see what happens. I do see that I might need to match up 3 column variables (in one row) to find a value that is unique. but this will help.