VBA code to search for string then a second string and return lines of data from a text file

Question

I am trying to search through large log files to find a string of text, then if that string is present to find another string of text and then return the next 5 lines of data. I have managed to search the text file for the string and return the 5 lines after however I cannot seem to get the macro to search for both lines of text before returning the 5 lines.

For example if the text file looked like this:

17:42:56: Log File Closed 17:42:56: PrintInvoice: 2 17:42:56: copyReportData: 17:42:56: getNextRptDataID: 17:42:58: CalcDelCharge: 17:42:58: Sub Total: 3.80 17:42:58: Del Total: 0.00 17:42:58: Disc Total: 0.00 17:42:58: Vat Total: 0.00 17:42:58: Inv Total: 3.80 18:33:00: CalculateAmtDue: 18:33:00: CalculateChange: 18:33:00: UpdateDelCharge: 18:33:00: UpdateTotals 18:42:58: CalcDelCharge: 18:42:58: Sub Total: 5.80 18:42:58: Del Total: 0.00 18:42:58: Disc Total: 0.00 18:42:58: Vat Total: 0.00 18:42:58: Inv Total: 5.80

I want to extract the 5 lines after the first 'CalcDelCharge' as it is follows 'PrintInvoice: 2', which is one of the strings I am also wanting to search for.

The text file contains 'CalcDelCharge' throughout, however I am only interested in the instances when it comes after 'PrintInvoice: 2', which appears much less frequently.

Here is what I have so far

Dim fn As String, txt As String, delim As String, a() As String
Dim i As Long, ii As Long, iii As Long, x, y
fn = "C:\Documents\tilllogfile.log"
delim = vbTab
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To 100)
For i = 0 To UBound(x)
    If InStr(1, x(i), "CalcDelCharge", 1) Then
    For ii = 0 To 5
        n = n + 1: y = Split(x(i + ii), delim)
        For iii = 0 To UBound(y)
            a(n, iii + 1) = y(iii)
        Next
    Next
End If

This will extract 5 lines after all 'CalcDelCharge' and put it into a spreadsheet for me, I have not been able to narrow it down to the instances when it follows 'PrintInvoice: 2'.

Any help would be greatly appreciated.

Thanks.

MarcinSzaleniec · Accepted Answer · 2017-11-16 13:14:27Z

1

Declare boolean variable to tell the macro if your text was found

Dim boolFound As Boolean

in your most external loop add first test:

For i = 0 To UBound(x)
    If InStr(1, x(i), "PrintInvoice: 2", 1) Then
        boolFound = True
    End If

in your second test add condition:

If InStr(1, x(i), "CalcDelCharge", 1) And boolFound Then

don't forget to change boolFound to false after you copy your five rows:

    boolFound = False
End If

answered Nov 16, 2017 at 13:14

MarcinSzaleniec

2,2661 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

danieltakeshi · Accepted Answer · 2017-11-16 15:23:56Z

You can use Regex, had to use 2 Regexs, however, it might be possible with just one.

Dim str1 As Variant, str2 As Variant
ReDim str1(0 To 100)
ReDim str2(0 To 100)
Dim objMatches As Object
Dim j As Long, k As Long
j = 0
k = 0
Set objRegExp = CreateObject("VBScript.RegExp") 'New regexp
objRegExp.Pattern = "(?:PrintInvoice: 2)[\s\S]*?(?:\s*(?:\d+:)+\s*[\w\s]*:\s\d.*)+" 'https://regex101.com/r/ChRr4w/1/
objRegExp.Global = True
Set objMatches = objRegExp.Execute(temp)
If objMatches.Count <> 0 Then
    For Each m In objMatches
        str1(j) = m.Value
        j = j + 1
    Next
    ReDim Preserve str1(0 To j - 1)
    For j = LBound(str1) To UBound(str1)
    txt = txt & str1(j) & vbCrLf
    Next j
End If
objRegExp.Pattern = "(?:\d+:)+\s*([\w\s]*:\s\d.*)" 'https://regex101.com/r/CLAL9i/1/
Set objMatches = objRegExp.Execute(txt)
  If objMatches.Count <> 0 Then
    For Each m In objMatches
        str2(k) = m.Submatches(0)
        k = k + 1
    Next
    ReDim Preserve str2(0 To k - 1)
    For k = LBound(str2) To UBound(str2)
    result = result & str2(k) & vbCrLf
    Next k
End If
Debug.Print result

Result

dwirony · Accepted Answer · 2017-11-16 18:43:15Z

Here is my version (with no booleans), just uses some nested loops. Here we place the values into an array for you to do whatever you want with:

Sample Data:

Option Explicit
Sub Test()
Dim searchvalue1 As String, searchvalue2 As String, myarray() As Variant, i As Long, j As Long, k As Long, l As Long

ReDim myarray(0 To 0)
searchvalue1 = "PrintInvoice: 2"
searchvalue2 = "CalcDelCharge:"
l = 1

For i = 1 To 100
    If InStr(Range("A" & i).Value, searchvalue1) > 0 Then
        For j = i + 1 To 100
            If InStr(Range("A" & j).Value, searchvalue2) > 0 Then
                For k = 0 To 4
                    ReDim Preserve myarray(UBound(myarray) + 1) As Variant
                    myarray(k) = Range("A" & j + l).Value
                    l = l + 1
                    Debug.Print myarray(k)
                Next k
            End If
        Next j
    End If
Next i

End Sub

Immediate window:

Collectives™ on Stack Overflow

VBA code to search for string then a second string and return lines of data from a text file

3 Answers 3

Comments

Result

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Result

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related