Search words in Text file

yinkajewole

Board Regular
Joined
Nov 23, 2018
Messages
209
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"
 
Last edited:

rlv01

Active Member
Joined
May 16, 2017
Messages
469
One way

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,919
Office Version
2007
Platform
Windows
Another way. Change data in blue by your information.

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,426
Office Version
2010
Platform
Windows
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...
Code:
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.
 
Last edited:

yinkajewole

Board Regular
Joined
Nov 23, 2018
Messages
209
@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.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,426
Office Version
2010
Platform
Windows
[/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

Well-known Member
Joined
Dec 3, 2018
Messages
7,919
Office Version
2007
Platform
Windows
@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

Active Member
Joined
May 16, 2017
Messages
469
FWIW, The code I posted requires the the Microsoft Scripting Runtime library (Tools->Reference, Check "Microsoft Scripting Runtime")
 

Forum statistics

Threads
1,078,442
Messages
5,340,305
Members
399,366
Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top