QTP Forum
Shopmotion


How to search for a particular value in an Excel sheet Empty
Search
 
 

Display results as :
 


Rechercher Advanced Search

Poll
Keywords

Latest topics
September 2019
MonTueWedThuFriSatSun
      1
2345678
9101112131415
16171819202122
23242526272829
30      

Calendar Calendar

Affiliates
free forum

Similar topics

    How to search for a particular value in an Excel sheet

    Go down

    How to search for a particular value in an Excel sheet Empty Re: How to search for a particular value in an Excel sheet

    Post by Gajre on Wed Jul 11, 2012 5:18 pm

    Use the below code with is made generic:

    strfilepath = "C:\Users\Srinivas.g\Desktop\List of QTP Topics - Learnt.xlsx"
    strSheetID = "QTP List of Topics Learnt"
    strSearchKey = "Yes"
    Call ExcelFindData(strfilepath, strSheetID, strSearchKey)

    Function ExcelFindData(strfilepath, SheetID, SearchKey)
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = True
    appExcel.DisplayAlerts = false
    'opens the sheet
    Set objWorkBook = appExcel.Workbooks.Open(strfilepath)
    'To select particular sheet
    Set objSheet = appExcel.Sheets(strSheetID)
    'select the used range in particular sheet
    With objSheet.UsedRange
    'data to find
    Set c = .Find (strSearchKey)
    'Loop through the used range
    For each c in objSheet.UsedRange
    'compare with the expected data
    If StrComp(c,strSearchKey,1) = 0 Then
    'mark the cell with blue color if it finds the data
    c.Interior.ColorIndex = 42
    End If
    'next search
    Set c = .FindNext(c)
    next
    End With
    objWorkBook.save
    objWorkBook.close
    appExcel.quit
    set appExcel=Nothing
    End Function
    Gajre
    Gajre
    Admin

    Posts : 37
    Join date : 2012-06-17
    Age : 36
    Location : Hyderabad

    View user profile https://qtpelearning.forumotion.com

    Back to top Go down

    How to search for a particular value in an Excel sheet Empty Re: How to search for a particular value in an Excel sheet

    Post by Gajre on Wed Jul 11, 2012 5:04 pm

    You generally need to use :
    Set c = objSheet.UsedRange.Find (“<Search Keyword>”) ''objSheet is your current working sheet object, refer the example.

    In this example, as we have used a With Statement above it, so we need to use:
    Set c = .Find(“<Search Keyword>”)

    Let me know if you have any other queries.

    Thanks,
    Witowrks
    Gajre
    Gajre
    Admin

    Posts : 37
    Join date : 2012-06-17
    Age : 36
    Location : Hyderabad

    View user profile https://qtpelearning.forumotion.com

    Back to top Go down

    How to search for a particular value in an Excel sheet Empty What is the proper way of using find method while working on excel objects?

    Post by gopi.clv87@gmail.com on Wed Jul 11, 2012 4:10 am

    Hi

    We used a couple of statements as mentioned below...
    Set c = .Find (“sg”)

    Do we need to write it as Set c = Excelapp.Find("sg") or just Set c = .Find (“sg”)


    Regards
    Gopi

    gopi.clv87@gmail.com

    Posts : 1
    Join date : 2012-07-03
    Age : 31
    Location : Hyderabad

    View user profile

    Back to top Go down

    How to search for a particular value in an Excel sheet Empty How to search for a particular value in an Excel sheet

    Post by Gajre on Wed Jul 04, 2012 3:28 am

    Set appExcel = CreateObject(“Excel.Application”)
    appExcel.visible=True
    'opens the sheet
    Set objWorkBook = appExcel.Workbooks.Open (filepath)
    'To select particular sheet
    Set objSheet = appExcel.Sheets(“Sheet1″)
    'select the used range in particular sheet
    With objSheet.UsedRange
    'data to find
    Set c = .Find (“sg”)
    'Loop through the used range
    For each c in objSheet.UsedRange
    'compare with the expected data
    If c=”sg” Then
    'make the gary color if it finds the data
    c.Interior.ColorIndex = 40
    End If
    'next search
    Set c = .FindNext(c)
    next
    End With
    objWorkBook.save
    objWorkBook.close
    appExcel.quit
    set appExcel=nothing
    Gajre
    Gajre
    Admin

    Posts : 37
    Join date : 2012-06-17
    Age : 36
    Location : Hyderabad

    View user profile https://qtpelearning.forumotion.com

    Back to top Go down

    How to search for a particular value in an Excel sheet Empty Re: How to search for a particular value in an Excel sheet

    Post by Sponsored content


    Sponsored content


    Back to top Go down

    Back to top

    - Similar topics

     
    Permissions in this forum:
    You cannot reply to topics in this forum