Try some thing like the following function. It will return the number of
timesFindWhat was found in the text file, where
FileName is the name of text file to search
FindWhat is the text to search for
Partial is True or False indicting whether to match FindWhat as a partial
string (e.g., if FindWhat is "abc", Partial = True indicates that "abcxyz"
will be counted as found. If Partial is False, "abcxyz" is not counted as a
match).
Compare is either vbBinaryCompare for case-sensitive comparison (e.g., "abc"
not equal "ABC") or vbTextCompare (e.g., "abc" = "ABC")
ClearPunctuation = True or False indicating whether the procedure should
ignore punctuation characters, replacing them with spaces. Puncuation
characters are listed in C_PUNCTUATION .
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
Function CountWords(FileName As String, FindWhat As String, _
Partial As Boolean, Compare As VbCompareMethod, _
ClearPunctuation As Boolean) As Long
Const C_PUNCTUATION = ".,;:\/|!@#$%^&*()""'"
Dim FNum As Integer
Dim InputLine As String
Dim Pos As Integer
Dim LineCount As Long
Dim FileCount As Long
Dim N As Long
Dim C As String
Dim ReadCount As Long
FNum = FreeFile()
Open FileName For Input Access Read As #FNum
Do
Line Input #FNum, InputLine
Debug.Print InputLine
ReadCount = ReadCount + 1
If ClearPunctuation = True Then
For N = 1 To Len(C_PUNCTUATION)
C = Mid(C_PUNCTUATION, N, 1)
InputLine = Replace(expression:=InputLine, Find:=C, Replace:="
", Compa=vbBinaryCompare)
Next N
End If
InputLine = " " & InputLine & " "
LineCount = 0
If Partial = True Then
Pos = InStr(1, InputLine, FindWhat, Compare)
Do Until Pos = 0
LineCount = LineCount + 1
Pos = InStr(Pos + 1, InputLine, FindWhat, Compare)
Loop
Else
Pos = InStr(1, InputLine, " " & FindWhat & " ", Compare)
Do Until Pos = 0
LineCount = LineCount + 1
Pos = InStr(Pos + 1, InputLine, " " & FindWhat & " ", Compare)
Loop
End If
FileCount = FileCount + LineCount
Loop Until EOF(FNum)
Close #FNum
MsgBox "Filename: '" & FileName & "' contains '" & FindWhat & "' " & _
Format(FileCount, "#,##0") & " times in " & _
Format(ReadCount, "#,##0") & " lines read."
CountWords = FileCount
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"Please help in building a Vba in excel" <Please help in building a Vba in
wrote in message
...
Hello,
Can anyone help me out creating a VBA in excel.
things to do :
1. Count the Word occarance in a text file. For example .."Load" may occur
250 times in a text file. would like to capture that count number and
place
that in a preferred column in an excel sheet.
pleass help
thanks