Compare a link name to a column in Excel

[Ankesh]

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