Excel VBA

css navigation by Css3Menu.com

Parse Text File

My assignment was to take a file that came out of an accounting system (Maxwell NGS2000), parse it out place the data into a spreadsheet.

The data as it comes out of the accounting system looks very un-professional but improves when I place it in my template.

    CompArray = Array(5, 33, 10, 10, 10, 10, 10, 9)
    SplitArray = Array(6, 12, 45, 57, 68, 81, 112, 122)
    PasteArray = Array(1, 2, 3, 4, 5, 6, 9, 10)  

       DetPlace = 0
                Do While DetPlace = 0
                    DetPlace = InStr(PayLine, " NUMBER        DESCRIPTION OF WORK           VALUE")
                    If DetPlace > 0 Then Exit Do
                    Line Input #1, PayLine
                Loop
                    If DetPlace > 0 Then
                        Line Input #1, PayLine
                        Line Input #1, PayLine
                        ItemLine = 16
                        Do While Mid(PayLine, 6, 5) > "     "
                            For S = 1 To UBound(PasteArray)
                                Cells(ItemLine, PasteArray(S)) = Mid(PayLine, SplitArray(S), CompArray(S))
                            Next
                 Cells(ItemLine, 7) = "=IF(ISERROR(" & Cells(ItemLine, 4).Address _
                              & "+" & Cells(ItemLine, 5).Address & "+" & Cells(ItemLine, 6).Address & "),0," _
                                & Cells(ItemLine, 4).Address _
                                & "+" & Cells(ItemLine, 5).Address & "+" & Cells(ItemLine, 6).Address & ")"
                 Cells(ItemLine, 8) = "=if(iserror(" & Cells(ItemLine, 7).Address & "/" & Cells(ItemLine, 3).Address _
                                & "),0," & Cells(ItemLine, 7).Address & " /" & Cells(ItemLine, 3).Address & ")"
    '=IF(ISERROR(G31/C31),0,G31/C31)
                        '    "=IF(ISERROR(D38+E38+F38),0,D38+E38+F38)"
                            ItemLine = ItemLine + 1
                            If ItemLine > 45 Then Exit Do
                            Line Input #1, PayLine
                        Loop
                    Else
                        Line Input #1, PayLine
                    End If

            Range(Cells(16, 2), Cells(ItemLine, 2)).Select
            Selection.Columns.AutoFit

This code fragment comes from looking for a particular string and grabbing what comes after it. Look for another fragment I have posted with a ditty to get the column numbers for each character in the report.

The 3 arrays determine where to grab within a line of input and how much to take.


© 2004-2017

Updated:  06/14/2017 17:53
This page added:  17 April 2004