Search words in Text file

[yinkajewole]

Assuming I want to search "He resides at Alaska"
How can i find this in a text file even when it is scattered all over in the file.
So that if each of these words are found in the text file, it should give me a message box "Yes"

 

[rlv01]

One way

Sub SimpleSearch()
    Dim myFSO As FileSystemObject
    Dim path As String, fileName As String
    Dim TSO As TextStream
    Dim Txt As String
    Dim SA, VA
    Dim I As Integer, J As Integer, Cnt As Integer

    path = "C:\Users\User1\Documents\"                'your path
    fileName = "tmp1.txt"                             'your file

    VA = Array("He", "resides", "at", "Alaska")

    Set myFSO = New FileSystemObject
    Set TSO = myFSO.OpenTextFile(path + fileName)
    Txt = TSO.ReadAll
    Txt = Replace(Txt, vbNewLine, " ")
    Txt = Replace(Txt, ".", " ")
    Txt = Replace(Txt, ",", " ")
    SA = Split(Txt, " ")

    Cnt = 0
    For I = LBound(VA) To UBound(VA)
        For J = LBound(SA) To UBound(SA)
            If SA(J) = VA(I) Then
                Cnt = Cnt + 1
                Exit For
            End If
        Next J
    Next I

    If Cnt = 4 Then
        MsgBox "Yes"
    End If
    TSO.Close
End Sub

 

[DanteAmor]

Another way. Change data in blue by your information.

Sub Macro4()
  Dim wPath As String, wFile As String, wString As String, f As Range, word As Variant, notE As Boolean
[COLOR=#0000ff]  [/COLOR]wPath[COLOR=#0000ff] = "C:\trabajo\"[/COLOR]
[COLOR=#0000ff]  [/COLOR]wFile[COLOR=#0000ff] = "test1.txt"[/COLOR]
[COLOR=#0000ff]  [/COLOR]wString[COLOR=#0000ff] = "He resides at Alaska"[/COLOR]
  
  Application.ScreenUpdating = False
  Workbooks.OpenText Filename:=wPath & wFile, Origin:=xlMSDOS
  For Each word In Split(wString, " ")
    Set f = Cells.Find(word, , xlValues, xlPart)
    If f Is Nothing Then notE = True
  Next
  ActiveWorkbook.Close
  If notE = False Then MsgBox "Yes" Else MsgBox "No exists"
End Sub

 

[Rick Rothstein]

I am assuming that you want to find each of those words as standalone words and not embedded within larger words. For example, if one of the words you want to find is "other" and that word is not in the text file, but the word "brotherhood" is in the text file... I am assuming you do not want the embedded word (shown in red) to be matched, correct? I also assume you do not want letter casing to matter. For example, if the word was at the beginning of a sentence, its first letter would be capitalized where as if it were an internal word to a sentence, its first letter would not be capitalized... I am assuming you would want the searched for word to match in either case, correct. I am also assuming that the word in the text file might not always be located next to a space, period or comma (it could be located next to a quote mark, dash, parentheses, or any other non-letter character), correct. If the answer to all of these questions is yes, then each of the routines posted so far are will not do what you want. Here is a macro that will...

Sub AreAllWordsInFile()
  Dim FileNum As Long, Num As Long, TotalFile As String, Word As Variant
  Dim wPath As String, wFile As String, wString As String
  wPath = "[B][COLOR="#FF0000"]c:\temp\[/COLOR][/B]"
  wFile = "[B][COLOR="#FF0000"]test.txt[/COLOR][/B]"
  wString = "He resides Alaska"
  FileNum = FreeFile
  Open wPath & wFile For Binary As #FileNum 
    TotalFile = Space(LOF(FileNum))
    Get #FileNum , , TotalFile
  Close #FileNum
  TotalFile = UCase(TotalFile)
  Num = 1
  For Each Word In Split(wString)
    Num = Num * (" " & TotalFile & " " Like "*[!0-9A-Za-z]" & UCase(Word) & "[!0-9A-Za-z]*")
  Next
  MsgBox Mid("No Yes", 1 + 3 * Abs(Num), 3)
End Sub

NOTE: Change the text in red to match your actual files path and name.

 

[yinkajewole]

@rlv01 Your code did not work
@DanteAmor Yours working perfectly as I wanted. I just did not like the idea of opening and closing of workbooks.
@Rick Rothstein the words must not necessarily be a complete words, it can be a part of the words
Above all, I have now altered my code to suit my need. Thank you all for getting some few ideas from your codes.

 

[Rick Rothstein]

[/COLOR]@Rick Rothstein the words must not necessarily be a complete words, it can be a part of the words
Above all, I have now altered my code to suit my need. Thank you all for getting some few ideas from your codes.

I am not sure what code you modified, but if you are interested, the only change that needs to be made to the code I posted is to replace the single line of code inside the For.,.Next loop with this one...

Num = Num * (InStr(1, TotalFile, Word, vbTextCompare) > 0)

 

[DanteAmor]

@rlv01 Your code did not work
@DanteAmor Yours working perfectly as I wanted. I just did not like the idea of opening and closing of workbooks.
@Rick Rothstein the words must not necessarily be a complete words, it can be a part of the words
Above all, I have now altered my code to suit my need. Thank you all for getting some few ideas from your codes.

I'm glad to help you. Thanks for the feedback.

 

[rlv01]

FWIW, The code I posted requires the the Microsoft Scripting Runtime library (Tools->Reference, Check "Microsoft Scripting Runtime")