Excel VBA ---Text File counting

[Guest]

Hello all
thanks in advance for reading this post-- hope it makes some sense

We have a folder which has log files. say folder "LOG" have files
"sample.log" and so on..
is there a way to count the number of times a specific text has occured in
that sample.log ...for example if there's a word "Load" in the log file.. is
theres a way that i can automate this process so that if i input the folder
to the VBA it generates the Number of "Load" in that sample.log and Displayes
the count in excel sheet at a specified location.

if i have many such log files in a same folder , is there a way to count all
the "load" text for every single file and place them seperatly for each.

Any help would be most appreciated,

Thanks

Ajay Tummala
Paceco Corp

 

[Tim Williams]

Try to stick to one thread.

Can "Load" appear multiple times on one line, or only once ?

 

[Guest]

This was my first post so was not sure, i could not find my post ,so did a
repost.

Can "Load" appear multiple times on one line, or only once ?

No, Load appears many times in the log file but not on one line.

Thanks for ur reply.

 

[Guest]

Specific words could be "Load" Discharge"...sanity etc
and the case is important.

thanks

 

[Tim Williams]

See if you can work with this:

'**********************
'Scan a directory and count the occurences of "Load"
Sub TestCount()

Const sDir As String = "C:\LOGS\"
Dim s As String
Dim sWord as string
Dim f As String
Dim oFSO As Object

sWord="Load"

Set oFSO = CreateObject("scripting.filesystemobject")

f = Dir(sDir & "*.txt")
If f <> "" Then
Do
s = oFSO.OpenTextFile(sDir & f).ReadAll
Debug.Print f & " = " & CountWords(s, sWord)
f = Dir()
Loop While f <> ""
End If

End Sub


'Count occurences of a word in another string
Function CountWords(TheContent As String, TheWord As String)

Static regEx As Object, matches As Object

If regEx Is Nothing Then
Set regEx = CreateObject("vbscript.regexp")
regEx.Global = True
regEx.IgnoreCase = False
End If
regEx.Pattern = "\b" & TheWord & "\b"

Set matches = regEx.Execute(TheContent)
CountWords = matches.Count

End Function
'************************

Tim



"Please help in building a Vba in excel"

 

[Chip Pearson]

See my code in reply to your other post. Please refrain from posting the
same question in different messages.


--
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"