VBA code to search txt file and display specific results in MsgBox

lizzie5 · Dec 7th 2015

Hello - I need help using VBA to search a txt file for specific text, and then if that text is found to display text from 2 rows above in a message box. I have found code to search a text file - see URL source below, as well as the code. The code works ok: it searches my text file for specific text ("COMMAND TEMPORARILY DISABLED"), and then it will display the line/row that that text is found in. I added code to the message box prompt to display the row number 2 rows above. What I am missing is code to specify that I want to see what's in the text file 2 rows above the found text. I don't care about the row numbers, I want to see what text the row contains.

I have attached a text file here. The rows I'm looking for are two rows above the found text, which in the attached example are:
** NEXT (ARZ) ?? REPGEN
** NEXT (NY2) ?? REPGEN

I'm actually specifically looking for the 3 characters in the parenthesis (i.e. ARZ & NY2), but if that can't be done it's fine to display the entire row's contents.

Lastly, I'd want to ensure the entire document is searched for the text I'm searching for, so if there is more than 1 result found it will display all of them, and not exit if/when one match is found.

I hope this makes sense. I have been scouring the web trying to find a way to output the found row's contents rather than the row number, but I can't find it. Help would be greatly appreciated!!

Code

Sub SearchTextFile()
'https://social.msdn.microsoft.com/Forums/en-US/62fceda5-b21a-40b6-857c-ad28f12c1b23/use-excel-vba-to-open-a-text-file-and-search-it-for-a-specific-string?forum=isvvba


'file path
Dim Path As String
Path = Sheets("master list").Range("M1").Value
Dim strFileName As String
strFileName = Path & "Dropbox\I6 - Canada\I6 Error Log\1Host Output\I6HOST.TXT"




    Const strSearch = "COMMAND TEMPORARILY DISABLED" 'text I am searching the I6HOST.TXT. file for
    Dim strLine As String
    Dim f As Integer
    Dim lngLine As Long
    Dim blnFound As Boolean
    Dim HostLine As Integer
    Dim HostText As String
    Dim HostResult As String




    f = FreeFile
    Open strFileName For Input As #f
    Do While Not EOF(f)
        lngLine = lngLine + 1
        Line Input #f, strLine
        If InStr(1, strLine, strSearch, vbBinaryCompare) > 0 Then
            HostLine = lngLine - 2  'the line of data I need is 2 lines above the lngLine where the search string is found
            MsgBox "Search string found in line " & lngLine & ". 3 character system name is in line " & HostLine, vbInformation 'MsgBox works fine, but I want it to show me the data in the HostLine (not tell me what row/line it is)
             blnFound = True
            Exit Do
        End If
    Loop
    Close #f
    If Not blnFound Then
        MsgBox "Search string not found", vbInformation
    End If
    
End Sub

Display More

**cytop** · Dec 7th 2015

Re: VBA code to search txt file and display specific results in MsgBox

The basic problem is the use of 'Line Input'. By the time you found something, the previous lines have already been discarded.

Try an alternative.

Code

Sub x()
     
     '// My temp
    Dim strFileName As String
    strFileName = "c:\temp\i6host.txt"
     
    Const strSearch = "COMMAND TEMPORARILY DISABLED" 'text I am searching the I6HOST.TXT. file for
    Dim strData          As String
    Dim vData            As Variant
    Dim strTemp As String
    Dim f                As Integer
    Dim lngLine          As Long
     
    Dim strMsg As String
    Dim lngfound As Long
     
    f = FreeFile
    Open strFileName For Input As #f
     
     '// Read the entire file to the string variable
    strData = Input(LOF(f), f)
    Close #f
     
     '// A quick and dirty check
    If InStr(strData, strSearch) = 0 Then
        MsgBox "No occurrances of '" & UCase(strSearch) & "' found in file: " & vbCrLf & _
        strFileName & "'"
        Exit Sub
    End If
     
     '// Split the string in the Variant array
    vData = Split(strData, vbCrLf)
     
     '// Starting from line 2 as you want the data from 2 lines above
     '// - so can't be any before that
    For lngLine = 2 To UBound(vData)
         '// String appears in this line?
        If InStr(vData(lngLine), strSearch) > 0 Then
             
            '// Make sure there's at least a '(' & ')' in the line
            If InStr(vData(lngLine - 2), "(") > 0 And InStr(vData(lngLine - 2), ")") > 0 Then
               strTemp = Mid(vData(lngLine - 2), InStr(vData(lngLine - 2), "(") + 1)
               strTemp = Left(strTemp, InStr(strTemp, ")") - 1)
            Else
               strTemp = vData(lngLine - 2)
            End If
            
             '// Update final message - but only so many. A messagebox is only so big
            If lngfound < 21 Then
                strMsg = strMsg & "Line: " & Right("00000" & CStr(lngLine), 5) & vbTab & strTemp & vbTab & vData(lngLine - 2) & vbCrLf
            End If
            '// but still inc counter
            lngfound = lngfound + 1
        End If
    Next
     
    strMsg = CStr(lngfound) & " line" & IIf(lngfound > 1, "s were ", " was ") & "found:" & vbCrLf & vbCrLf & strMsg
    If lngfound > 20 Then
        strMsg = strMsg & vbCrLf & "+ " & CStr(lngfound - 20) & " other matches."
    End If
     
    MsgBox strMsg, vbInformation, "Finished"
     
End Sub

Display More

This includes the extracted string and the full line contents, just to confirm. Easy enough to change later.

lizzie5 · Dec 7th 2015

Re: VBA code to search txt file and display specific results in MsgBox

Wow, thank you very much! This looks like it works great. I have a question regarding this part of the code and your note:

Code

If InStr(vData(lngLine), strSearch) > 0 Then
             '// Update final message - but only so many. A messagebox is only so big
             
            If lngfound < 21 Then
                strMsg = strMsg & "Line: " & Right("00000" & CStr(lngLine), 5) & vbTab & vData(lngLine - 2) & vbCrLf
            End If
            lngfound = lngfound + 1
        End If

Does the "<21" mean it will only account for up to 21 occurrences of finding that string, like you mentioned in your note about the messagebox is only so big?

In my case, while it would be rare to see so many matches, there would never be more than 35 instances of the found message appearing, so I am assuming I can change "<21" to "<36"?

Thanks again.

**cytop** · Dec 7th 2015

Re: VBA code to search txt file and display specific results in MsgBox

Arbitrary limit as I don't know the screen resolution you're using. By all means change, but don't forget to change the other calcs ( > 20) ...

You can use Notepad to copy paste a bunch of extra records in a copy of the test file to check what you can view on your monitor (I did).

lizzie5 · Dec 7th 2015

Re: VBA code to search txt file and display specific results in MsgBox

Thanks cytop.

**cytop** · Dec 7th 2015

Re: VBA code to search txt file and display specific results in MsgBox

You're welcome.

VBA code to search txt file and display specific results in MsgBox

Participate now!

Share