Tuesday, January 1, 2013

How to display occurances of a string in a Excel file

Following code helps find the occurrences of a string in a Excel file.

It will return the count as 0, if the string is not found
else returns the number of occurrences of the string

Dim oXLObj,oXLWBObj,olXLWSObj

Function FindStringOccuranceCount(sFileName,iSheetId,sSearchString)
    iCount=0
    set oXLObj=createobject("Excel.Application")
    Set oXLWBObj=oXLObj.workbooks.open(sFileName)
    Set olXLWSObj=oXLWBObj.worksheets(1)

    set cell=olXLWSObj.Range("A:Z").find(sSearchString)
   

    If cell is nothing Then
        CloseExcel()
        FindStringOccuranceCount=iCount
        Exit Function
    End If

    sFirstAddress=cell.address

    Do
        set cell=olXLWSObj.Range("A:Z").FindNext(cell)
        'set CurCell=olXLWSObj.UsedRange.FindNext(sSearchString)
        sCurrentAddress=cell.address
        'sCurrentAddress
        iCount=iCount+1
    loop while not cell is nothing and sCurrentAddress<>sFirstAddress

    CloseExcel()
    FindStringOccuranceCount=iCount

End Function

Function CloseExcel()
    oXLWBObj.close
    oXLObj.application.quit
    Set oXLWBObj=nothing
    Set oXLObj=nothing
End Function

x=FindStringOccuranceCount("C:\Test1.xls",1,"Uday")
msgbox x

Sample Excel file is here:

No comments: