How can I count the nmber of occurances of a word in a text file using Access VBA?

[vgnadeau]

Hi. I would like to know how to count the number of occurances of a word in a text file.

I am using Microsoft Access VBA.

Any ideas?

[ADezii]

Hi. I would like to know how to count the number of occurances of a word in a text file.

I am using Microsoft Access VBA.

Any ideas?

I'll keep the Logic parallel to your previous Thread:

'Set a Reference to the Microsoft Scripting Runtime Library
Dim objFSO As FileSystemObject
Dim ts As TextStream
Dim strText As String
Dim strSearchText As String
Dim strFileName As String
Dim lngPos As Long
Dim lngStringCount As Long

strSearchText = "cb: TRINGLE"
  
strFileName = CurrentProject.Path & "\Test.txt"

'Create instance of FileSystemObject.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ts = objFSO.OpenTextFile(strFileName, ForReading)
  
'Read entire contents of file, save to strText variable
strText = ts.ReadAll

lngPos = 1

Do
  lngPos = InStr(lngPos, strText, strSearchText)
    If lngPos > 0 Then
      lngStringCount = lngStringCount + 1
      lngPos = lngPos + Len(strSearchText)
    End If
Loop Until lngPos = 0

MsgBox "The String [" & strSearchText & "] appears " & lngStringCount & " time(s) in the " & _
       "File " & strFileName & "!"

[vgnadeau]

You are awesome, thanks so much for your help!

[ADezii]

You are awesome, thanks so much for your help!

You are very welcome.

[vgnadeau]

Ok. Last problem of the day. Now I want to be able to repeat the cycle. I've counted the numner of occurances of "cb:TRINGLE " and divided the result by 4 to give me the number of times I need to repeat the Find/replace. So, I want to replace the first and fourth insances and then the first and fourth again ...

Example:
cb:MATERIAL
cb:TRINGLE
cb:TRINGLE
cb:MATERIAL
cb:MATERIAL
cb:TRINGLE
cb:TRINGLE
cb:MATERIAL
cb:MATERIAL
cb:TRINGLE
cb:TRINGLE
cb:MATERIAL

Here's my code so far;

Function FindAndReplace()
Dim sSearchText As String
Dim sReplaceText As String
Dim sFileName As String
Dim lngPosition As Long
Dim intNumOfMatches As Integer
Dim strText As String
Dim strNew As String
Dim lngNewPos As Long
Dim strMsg As String
Dim intCount As Integer

Const ForReading = 1
Const ForWriting = 2
  
sSearchText = "cb:TRINGLE"
sReplaceText = "cb:MATERIAL"
  
sFileName = "C:\test.txt"
[B]intCount = CountWordOccurances(sSearchText, sFileName)
intCount = intCount \ 4[/B]


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ts = objFSO.OpenTextFile(sFileName, ForReading)
strText = ts.ReadAll


lngPosition = InStr(strText, sSearchText)
If lngPosition = 0 Then Exit Function

strNew = Left$(strText, lngPosition - 1) & sReplaceText & _
               Mid$(strText, lngPosition + Len(sSearchText))

Starting
intNumOfMatches = intNumOfMatches + 1
lngNewPos = lngPosition + 1

Do
  If InStr(lngNewPos, strText, sSearchText) <> 0 Then
    lngPosition = InStr(lngNewPos, strText, sSearchText)
    intNumOfMatches = intNumOfMatches + 1
      lngNewPos = lngPosition + 1
        If intNumOfMatches = 4 Then     '4th occurrence
          strNew = Left$(strNew, lngPosition - 1) & "/" & sReplaceText & _
                   Mid$(strNew, lngPosition + Len(sReplaceText))
        End If
  End If
Loop Until intNumOfMatches = 4 Or lngPosition = 0

Set objFile = objFSO.OpenTextFile(sFileName, ForWriting)
objFile.WriteLine strNew

objFile.Close

End Function

Function CountWordOccurances(WordToCount As String, Filename As String) As Integer
Dim strText As String
Dim strSearchText As String
Dim strFileName As String
Dim lngPos As Long
Dim lngStringCount As Long
  
strSearchText = WordToCount
strFileName = Filename
  
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ts = objFSO.OpenTextFile(strFileName, 1)
  
strText = ts.ReadAll
  
lngPos = 1
  
Do
  lngPos = InStr(lngPos, strText, strSearchText)
    If lngPos > 0 Then
      lngStringCount = lngStringCount + 1
      lngPos = lngPos + Len(strSearchText)
    End If
Loop Until lngPos = 0
  
End Function

I don't know what to do next ...

[ADezii]

1. You should have clearly stated your Primary Objective up front and saved me some extra effort. I am referring to this new Cycle Requirement which I have heard for the first time.
2. This Thread is clearly related to your initial Thread on this Topic, and you should not have split it off.
3. The above being said, you do not need to calculate the number of occurrences of one String in another. I am referring to the Routine that I created for you which really is not needed.
4. The simplest approach is to build the Logic into the Loop itself, namely: if a Match Number MOD 4 = 0 it gets Replaced. Match 1 is handled independently, but if a Match Number is 5, 9, 13, 17 ..., etc. it also gets replaced since it is the start of a New Cycle of 4.
5. Study the revised Code Segment which currently allows for 6 Cycles, and download the New Attachment. Again, Unzip 'both' Files to the same Directory.
   

   Do
     If InStr(lngNewPos, strText, sSearchText) <> 0 Then
       lngPosition = InStr(lngNewPos, strText, sSearchText)
       intNumOfMatches = intNumOfMatches + 1
         lngNewPos = lngPosition + 1
           If intNumOfMatches Mod 4 = 0 Or intNumOfMatches = 5 Or intNumOfMatches = 9 Or intNumOfMatches = 13 _
                              Or intNumOfMatches = 17 Or intNumOfMatches = 21 Then
             strNew = Left$(strNew, lngPosition - 1) & sReplaceText & " " & _
                      Mid$(strNew, lngPosition + Len(sSearchText) + 1)
           End If
     Else
       lngPosition = 0
     End If
   Loop Until lngPosition = 0

[vgnadeau]

Well ...
1) I did not know that I would have to 'cycle' until after I had originally posted my question. The XML file I was given was a sample. It's the sort of situation where if I ask the question I get the answer, if I don't then no one voluteers the info.
2) I was actually thinking about the usefulness of the answers for someone who was searching for a specific topic (Counting the number of occurances is not the same as find/replace).
3) I do need to calculate the number of ocurrances because I do not know how many times <cb:TRINGLE> will be in the file. I only want to change the first and fourth, first and fourth, etc. That's why I thought I should count the number of occurances and devide by 4. After that I did not know how to use this info to properly cycle through your code.

I do appreciate your help but ... well if you don't like accumulative questions from newbies perhaps you shoulden't answer them? I am learning something from each of your answers.

[vgnadeau]

Yes, I have typos in my previous message. I think common sense will tell you what I meant.