Tuesday, April 19, 2011

QTP code to search data from Excel sheet

Dim appExcel, objWorkBook, objSheet, columncount, rowcount,

'Create the application object for excel
Set appExcel = CreateObject("Excel.Application")

'Set the workbook object by opening 
Set objWorkBook = appExcel.Workbooks.open("c:\Smoke_Test\SmokeTest.xls")

'Set the Worksheet object
Set objSheet = appExcel.Sheets("Global")

'Get the count for total used columns
columncount = objSheet.usedrange.columns.count

'Get the count for total used rows
rowcount = objSheet.usedrange.rows.count

'Assign the data to search for
Find_Details="Report"

'Iterate the loop through each cell to find out required data 
For a= 1 to rowcount
For b = 1 to columncount
fieldvalue =objSheet.cells(a,b)
If  cstr(fieldvalue)= Cstr(Find_Details) Then
msgbox cstr(fieldvalue)
Exit For
End If
Next
Next

Click here if you are interested in similar QTP scripts

7 comments:

  1. Hi jyotsna,

    I have tried the above codes but it is giving an error in 7th line. I have created new excel file(Test.xls)and stored in C drive and so in program i have given the path "C:\Test.xls" but still it is giving an error. Can u help me out from this.

    Thankyou
    shiri(tshiri4@gmail.com)

    ReplyDelete
  2. Check the extension of excel file(.xls or .xlsx )and rename Sheet1 to Global if u want to run same code.

    ReplyDelete
  3. Hi Anonymous
    I have checked the extension its xls only and in the code i have given the name as sheet1 only.
    but still its not working.

    Thankyou,
    Shiri

    ReplyDelete
  4. Hi Anonymous
    The script is working fine.
    Thanks a lot
    Shiri

    ReplyDelete
  5. @ shiri

    Hi
    Post the Script that is working

    ReplyDelete
  6. Hi,

    On running the above script, I am getting errors in the following lines:

    Set objSheet = appExcel.Sheets("Global")
    columncount = objSheet.usedrange.columns.count
    rowcount = objSheet.usedrange.rows.count

    Could someone please take a look?

    ReplyDelete
  7. This works fine for me, please check if you have MS Excel installed on the machine + ensure you are using *.xls file and not *.xlsx

    ReplyDelete

Please leave your comment here...