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:
Post a Comment