Excel VBA Code to Open a .txt file and loop through every single words

[DavidLee91]

Hi Folks,

I need to write a VBA code that would open a ReportLog.txt file and looping though every single word to find a specific word I need.

For example, in ReportLog.txt file contains:

Report data input... ... ... .... ... ERROR...
Somemore information input........

I would need to detect the word "Error" in this .txt file and return "Error found" back to my Excel sheet Range "D1". I have the following code, however, it is taking line by line which doesnt allow me to do a IF ELSE statement to find the word "Error".

I would really appreciate if any kind soul can point me to the right direction or help me with the code!

Thanks people!!

PS: This code was not coded by me originally. I took it to edit the code.

Sub ReadLog()
Dim FilePath As String
Dim strLine As String
Dim i As Integer
FilePath = "D:test2.txt"
Open FilePath For Input As #1
i = 1
While EOF(1) = False
    'read the next line of data in the text file
    Line Input #1, strLine
    
    If strLine = "Error" Then
        Range("D1").Value = "Error found"
    Else: End If
    
  
    i = i + 1
Wend
Close #1
End Sub

[p45cal]

minimally changing your code: (Changed the While..Wend to a Do While..Loop so that we can jump out of it if the string is found):

Sub ReadLog()
Dim FilePath As String
Dim strLine As String
Dim i As Integer
FilePath = "D:test2.txt"
Open FilePath For Input As #1
i = 1
Do While EOF(1) = False
  'read the next line of data in the text file
  Line Input #1, strLine

  If InStr(1, strLine, "Error", vbTextCompare) > 0 Then
    Range("D1").Value = "Error found"
    Exit Do ' once found we don't need to keep on looking.
  End If
  i = i + 1
Loop
Close #1
End Sub

Completely untested.
Don't know what you're using the variable i for. There's probably a faster way of looding the whole file and searching for 'Error'.

[DavidLee91]

Hi p45Cal,

THANKS so so so much! It works wonder!

Just for education sake, what is probably the faster way of looding the whole file and searching for 'Error'?

In addition, how can I edit the following code if I want to have more than 1 condition? For example, if the text file contains "Error", it will go range "D1" and states "Error found". If it containts "warning", it will go range "D1" and states "warning found" instead?

If InStr(1, strLine, "Error", vbTextCompare) > 0 Then

Thank you for your time!

Regards,
David

[p45cal]

Just for education's sake, what is probably the faster way of loading the whole file and searching for 'Error'?

Again, untested, but:

Sub ReadLog2()
Dim FilePath As String
Dim strLine As String
FilePath = "D:test2.txt"
Open FilePath For Input As #1
strLine = Input(LOF(1), #1)
Close #1
myResult = "Nothing Found"
If InStr(1, strLine, "Error", vbTextCompare) > 0 Then
  myResult = "Error found"
ElseIf InStr(1, strLine, "Warning", vbTextCompare) > 0 Then
  myResult = "Warning found"
End If
Range("D1").Value = myResult
End Sub

In addition, how can I edit the following code if I want to have more than 1 condition? For example, if the text file contains "Error", it will go range "D1" and states "Error found". If it containts "warning", it will go range "D1" and states "warning found" instead?

Try (untested of course):

Sub ReadLog()
Dim FilePath As String
Dim strLine As String
FilePath = "D:test2.txt"
Open FilePath For Input As #1
Do While EOF(1) = False
  'read the next line of data in the text file
  Line Input #1, strLine
  If InStr(1, strLine, "Error", vbTextCompare) > 0 Then
    Range("D1").Value = "Error found"
    Exit Do  ' once found we don't need to keep on looking.
  ElseIf InStr(1, strLine, "Warning", vbTextCompare) > 0 Then
    Range("D1").Value = "Warning found"
    Exit Do  ' once found we don't need to keep on looking.
  End If
Loop
Close #1
End Sub

Whether it'll be faster or not might be difficult to gauge; if the text file is really massive then it'll be resource hungry and may slow the computer, but if it's massive and the Warning/Error is near the beginning of the file then the line by line approach might be quicker.

[snb]

This is all you need:

Sub M_snb() 
   c00=lcase(createobject("scripting.filesystemobject").opentextfile("D:\test2.txt").readall)
   if instr(c00,"error") then cells(1,4)="Error found"
   if instr(c00,"warning") then cells(1,4)="Warning found"
End sub

[DavidLee91]

Hi p45Cal,

Thank you so so much for your help!

Hi snb,

That WORK PERFECTLY! Thanks so much for your time!

To All Readers:

All of the code above by p45cal and snb have been tried and tested. Credit to p45cal and snb!

You guys are awesome