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

  • 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!!

  • 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.

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

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

    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.

  • 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).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!