VBA Macro : catch a line with specific string and return it

Question

im trying to catch simulation results from a simulation report (.log).I want to match "passed" and "failed" strings and to save the line where the match were done in an other results file that i will use for further operations in excel. I couldn't match the desired lines: the following sub that i wrote writes not only the desired lines but all the lines. i dont know where the problem is..

Sub generate_sim_results()

    Dim testResults  As String
    Dim InputFile As String
    Dim fso As Object
    Dim ObjFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")


    testResults = Application.ActiveWorkbook.Path & "\sim_results.log"
    If Len(Dir$(testResults)) > 0 Then
        Kill testResults
    End If


    InputFile = Dir(Application.ActiveWorkbook.Path & "\" & "*.log")
    InputFile = Application.ActiveWorkbook.Path & "\" & InputFile
    'Debug.Print InputFile

    fnum = FreeFile()
    Open InputFile For Input As #fnum
    Set ObjFile = fso.CreateTextFile(testResults)

    While Not EOF(fnum)

        Line Input #fnum, dataLine
        If InStr(1, dataLine, "passed", vbTextCompare) > 0 Or InStr(1, dataLine,"failed", vbTextCompare) > 0 Then
            ObjFile.Writeline dataLine
            Debug.Print dataLine
        End If

    Wend

    ObjFile.Close
    Set fso = Nothing
    Set ObjFile = Nothing

End Sub

*sample of the log file*
<time="1500 ns" instance="testcase_00">passed
<time="342100480 ps" instance="testcase_01">passed
blabla informations about the tests....
<time="742894 ns" instance="testcase_02_01">passed
blabla informations about the tests....
blabla informations about the tests....
<time="744121040 ps" instance="testcase_02_02">failed
blabla informations about the tests....
<time="745034560 ps" instance="testcase_02_03">passed
blabla informations about the tests....
<time="745134560 ps" instance="testcase_02_04">passed
blabla informations about the tests....
blabla informations about the tests....
blabla informations about the tests....
blabla informations about the tests....
<time="745548080 ps" instance="testcase_03">failed
<time="747388640 ps" instance="testcase_04_01">passed
<time="750745100 ns" instance="testcase_04_02">passed
blabla informations about the tests....

want to return only the lines with the passed and failed strings and write it in another log file (sim_results)

This is likely going to get impossible to answer without a sample of the input .log file and a description of what your criteria are for lines to include. — Comintern, Aug 05 '18 at 18:15
Code works for me. The strange thing I noticed is the two lines `InputFile = Dir(Application.ActiveWorkbook.Path & "\" & "*.log") InputFile = Application.ActiveWorkbook.Path & "\" & InputFile` What is that good for? You just get the first file in the directory with the ending log. Is that the right one? — Storax, Aug 05 '18 at 18:43
The InputFile = Dir(Application.ActiveWorkbook.Path & "\" & "*.log") returns only the file name (basename) and not the path thats why i used the second one to get the whole file name path to read the file . Did you get the Output file with the correct contents : failed and passed lines ? — ES87ME, Aug 05 '18 at 19:00
Yes I only got failed and passed lines. Is it possibe that you have a "Unix"-file? — Storax, Aug 05 '18 at 19:44

Storax · Accepted Answer · 2018-08-05T19:56:34.777

When reading a file with Line Input you have to take into consideration that Line Input will only stop when it reads a CR or a CR and LF but it will not stop in case it only reads a LF. In your case you need to convert th LFs to a CR and LF. You can do that with Notpad++

As you already use FSO you could also use it to read the file like this. Then you do not need to bother about "Unix".

Sub generate_sim_results()

Dim testResults As String
Dim InputFile As String
Dim fso As Object
Dim ObjFile As Object
Dim fnum As Long
Dim dataLine As String
    Set fso = CreateObject("Scripting.FileSystemObject")


    testResults = Application.ActiveWorkbook.Path & "\sim_results.log"
    If Len(Dir$(testResults)) > 0 Then
        Kill testResults
    End If


    InputFile = Dir(Application.ActiveWorkbook.Path & "\" & "*.log")
    InputFile = Application.ActiveWorkbook.Path & "\" & InputFile
    'Debug.Print InputFile

    fnum = FreeFile()
    Open InputFile For Input As #fnum
    Set ObjFile = fso.CreateTextFile(testResults)

    Dim myFile
    Set myFile = fso.OpenTextFile(InputFile, 1)
    Do While myFile.AtEndOfStream <> True
        dataLine = myFile.ReadLine
        If InStr(1, dataLine, "passed", vbTextCompare) > 0 Or InStr(1, dataLine, "failed", vbTextCompare) > 0 Then
            ObjFile.Writeline dataLine
            Debug.Print dataLine
        End If

    Loop
    myFile.Close
    ObjFile.Close
    Set fso = Nothing
    Set ObjFile = Nothing

End Sub

u have wright..it is an Unix file. Thanks for the Help! – ES87ME Aug 05 '18 at 20:14 — ES87ME, Aug 05 '18 at 20:14

VBA Macro : catch a line with specific string and return it

1 Answers1