ExcelBanter - Help with creating a VBA in Excel .

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