Excel VBA code to break and read text file line by line

learnerjane 

Here you go.

Sub ProcessTextFile()
    Dim sFile As String
    Dim f As Integer
    Dim sLines As String
    Dim aLines() As String
    Dim aLine() As String
    Dim i As Long
    Dim n As Long
    Dim sTitle As String
    ' *** Change sFile to the correct path and filename ***
    sFile = "Test.txt"
    f = FreeFile
    Open sFile For Input As #f
    sLines = Input(LOF(f), #f)
    Close #f
    aLines = Split(sLines, vbCrLf)
    For i = 0 To UBound(aLines)
        If aLines(i) <> "" Then
            aLine = Split(aLines(i), "|")
            Select Case aLine(0)
                Case "HEAD"
                    If n > 0 Then
                        Debug.Print "Group " & sTitle & " has " & n & " items"
                    End If
                    sTitle = aLine(1)
                    n = 0
                Case "DET"
                    n = n + 1
            End Select
        End If
    Next i
    Debug.Print "Group " & sTitle & " has " & n & " items"
End Sub