Excel VBA ---Text File counting

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Try to stick to one thread.

Can "Load" appear multiple times on one line, or only once ?
 
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.
 
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"
 
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"
 
Back
Top