Quick test professional

QTP Tips QTP codes QTP Faqs and more

Search for a particular value in Excel

Set appExcel = CreateObject(“Excel.Application”)
 Set objWorkBook = appExcel.Workbooks.Open (filepath)’opens the sheet

 Set objSheet = appExcel.Sheets(“Sheet1”)’ To select particular sheet
With objSheet.UsedRange ‘ select the used range in particular sheet
    Set c = .Find (“nn”)’ data to find  
For each c in objSheet.UsedRange’ Loop through the used range
 If c=”nn” then’ compare with the expected data
            c.Interior.ColorIndex = 40′ make the gary color if it finds the data
End If
            Set c = .FindNext(c)’ next search
End With
set appExcel=nothing

Author:Mohan Kakarla


October 21, 2008 - Posted by | Excel Automation, Uncategorized | , ,


  1. hey can you help me in this issue ?
    i have a batch process which runs for 8 hours .Now the developement of the application is complete .It’s in testing phase.while testing(automation testing , with any tool )can the script call the batch process in and can the script wait for results from batch process for such a long time ?

    Comment by meyya | November 24, 2008 | Reply

  2. meyya,
    yes it can wait , for that use shell object

    Dim oShell
    Set oShell = CreateObject (“WSCript.shell”)
    oShell.run “cmd /K CD C:\Documents and Settings\kmohankumar\Desktop & 2.vbs”,,true’ here true indicates wait until this step completes, if u provide false here the qtp does not wait till it completes
    Set oShell = Nothing
    msgbox “hi”

    Comment by quicktestprofessional | November 25, 2008 | Reply

  3. Hi,
    Can you please help me with a problem that I’m having with QTP? I need to loop through an excel sheet with data and create an array with required data.

    I hope you can understand my question. Please let me know if you cannot. I’ll try in a different way. Thanks.

    Comment by Sumi | March 25, 2009 | Reply

  4. Hi
    Good one
    it will be more fast if you use this

    Set appExcel = CreateObject(“Excel.Application”)
    Set objWorkBook = appExcel.Workbooks.Open (filepath)
    Set objSheet = appExcel.Sheets(“Sheet1”)

    set objValueFind = objectSheet.UsedRange.Find(“rajiv”)
    If Not objValueFind Is Nothing Then
    rem find the address
    firstAddress = objValueFind.Address
    rem make the gary color if it finds the data
    objValueFind.Interior.ColorIndex = 40
    Set objValueFind = .FindNext(objValueFind)
    Loop While Not objValueFind Is Nothing And objValueFind.Address firstAddress
    End If

    Comment by rajivkumarnandvani | April 25, 2009 | Reply

    • I am unable to run it, as there might be some issue with Do loop.. can you please help me

      There is no quliflier in below line

      Loop While Not objValueFind Is Nothing And objValueFind.Address????firstAddress

      Comment by hari | April 12, 2011 | Reply

  5. Hi,

    Can u please help me with this. I need to find the repeated entries also. The above code searches for a text but it colors only one again and again when multiple entries of the text are present.

    Comment by Sapna | May 28, 2009 | Reply

  6. Hello,
    I’m a new in QTP. please help me, I wants write a test case in google web page, how to I write it or what is the procedure……….
    Please help me………..

    Comment by Manish Pathak | October 5, 2009 | Reply

  7. hi mahan and all,

    if we release parents memory does it really release the child object memory or we need to manually release the child objects memory too ?

    Comment by Baswaraj | February 6, 2010 | Reply

  8. hi mohan,

    i want to search for a particular number like 235613 in an excel sheet… if it is not found in the first sheet i have to look into the second sheet until i get that no….
    i have used the code given above.. but the problem is i am exporting some information into the excel sheet.. in that excel sheet only i am searching for a particular value as i said earlier… the problem exist here…the file name and the sheet name are same… so i am getting an error in this line (Set objSheet = appExcel.Sheets(“Sheet1″))… instead of Sheet1 i have used the file name but still showing the error as “Subscript out of range”….. help me out yaar….
    help me out in this…

    Comment by Kiruthika | March 18, 2010 | Reply

  9. Hi All,

    Can any one explain me how to validate each cell when the excel sheet is loaded into data table where i have to validate the each cell before set the value to the application.

    Any one have answer.Waiting for your repaly.

    Thank You

    Comment by Jagath | August 27, 2010 | Reply

  10. Can we find the row number and Column number using the .Find(“abc”) logic.
    I mean to ask after value is searched using .Find(“”) method.Will it give the row and Column number value.

    Comment by Abhimanyu | September 20, 2010 | Reply

  11. Hi,

    Search for a particular value in Excel, Descriptive Programming for “Search for a particular value in Excel” is not working.

    Comment by Ashwini | November 11, 2010 | Reply

  12. Hi, I would like Thanks and Appreciate you stuff which u given “Search for a particular value in Excel” .

    I was struggling to find the cell number with match string . Thanks I got in you block.

    I want the code to Minimize and Maximize the Browser (IE version 7) if there are multiple site are open in Browser.

    Advance Thanks

    Comment by Imtiaz | February 27, 2011 | Reply

  13. hi,
    i want help for checking PDF file contents like header contents, footer contents, data, column length, spacing between two text using vb scripting.

    your help will be really appreciated

    Comment by lds | April 2, 2011 | Reply

  14. none of the above codes are working… please help and post the correct one. I want to search for a text = “.length()” in the excel sheet and also need to check that the is of red color or not. I used the code below but it’s not working. please help:

    On Error Resume Next
    Set objExcelApp = createobject(“Excel.Application”)
    Set objWorkbook = objExcelApp.Workbooks.Open(“C:\Shalabh\Automation Framework\Templates\DIA_Proposed_Mapping_Template_GHEPM.xls”)
    Set objWorksheet = objWorkbook.Worksheets(“DIA Mapping”)

    set objValueFind = objWorksheet.UsedRange.Find(“.length()”)

    If Not objValueFind Is Nothing Then
    rem find the address
    firstAddress = objValueFind.Address
    rem make the gray color if it finds the data
    MsgBox “value Found”
    objValueFind.Interior.ColorIndex = 40
    Set objValueFind = .FindNext(objValueFind)
    Loop While Not objValueFind Is Nothing And objValueFind.Address firstAddress
    End If


    Comment by Shalabh Dixit | June 10, 2011 | Reply

  15. […] Search for a particular value in Excel […]

    Pingback by tallada | September 18, 2012 | Reply

  16. I am getting the following run time effor when i am running the following script:-
    Error msg:-
    Subscript out of range

    Line (6): “objWorkbook1.Worksheets(“Sheet1”).Range(“B1″).PasteSpecial Paste =xlValues”.


    Set objExcel = CreateObject(“Excel.Application”)
    objExcel.Visible = True
    Set objWorkbook2= objExcel.Workbooks.Open(“C:\TEST\Practise\testrun.xls”)
    Set objWorkbook1= objExcel.Workbooks.Open(“C:\TEST\Practise\res.xls”)
    objWorkbook1.Worksheets(“Sheet1”).Range(“B1”).PasteSpecial Paste =xlValues
    set objExcel=nothing

    Please help i am new to qtp and vbscripting

    Comment by David | October 1, 2012 | Reply

  17. please help me..
    need to create Function/Sub for below script to use call or execute in qtp framework.

    ‘Declaring excel sheet
    Dim objExcel, objWorkBook, objWorkSheet, i

    Set objExcel=CreateObject(“excel.application”)

    Set objWorkBook=objExcel.Workbooks.open(“C:\ZCWebDataTable2.xls”)

    Set objWorkSheet=objWorkBook.Worksheets(“ZCReq”)


    For i= 1 to Row

    For j=1 to Col

    Msgbox mysheet.cells(i,j).value



    ‘Get URL from excel and open
    systemutil.Run “iexplore.exe”, objWorkSheet.cells(2,”B”)

    Comment by tapas | April 23, 2013 | Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: