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 & ")"
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.
|