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