Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Compare a link name to a column in Excel
#1
Solved: 11 Years, 3 Months, 1 Week ago

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?
Reply
#2
Solved: 11 Years, 3 Months, 1 Week ago
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.
Reply
#3
Solved: 11 Years, 3 Months, 1 Week ago
Ankesh,

Ok, greast but...

How do I do the compaireson of a link name to an entire coulmn of data?

thank you ... ;-)
Reply
#4
Solved: 11 Years, 3 Months, 1 Week ago
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
Reply
#5
Solved: 11 Years, 3 Months, 1 Week ago
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.

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [UFT] Get column name from SfwObject felino 0 2,998 12-02-2015, 04:07 PM
Last Post: felino
  Compare WebTable Elements saraiado 1 2,545 06-11-2015, 06:54 PM
Last Post: venkatesh9032
  How to compare two binary values Naresh 0 2,331 09-09-2014, 05:06 PM
Last Post: Naresh
  Unable to right click column header sudhirzpatil 4 6,356 07-18-2013, 09:42 PM
Last Post: sudhirzpatil
  datatable column exist diya 3 12,504 12-11-2012, 11:24 AM
Last Post: elango87

Forum Jump:


Users browsing this thread: 1 Guest(s)