Text File Specifications

[Victor Moss]

Hi guys,

I've been streaming Excel Data to a Text files for years, the Text file consists of a Header Record, Detail Records and a Trailer Record, it's been working great, I've now had to start adding Contra Records to any variable number of transactions, my problem is I don't know how to Loop the them in between the Detail Records.

*	WD50	TEST	20210514	Y	TEST	+	61	0	01	LIVE	2	WD50	T	1425502
2	WD50	470010	0PHA003	1225422872	1	461783	MS HAIBO	SALARY
2	WD50	470010	0MHL011	1361885910	1	461783	MS WENA	SALARY
2	WD50	51001	0LIM005	12249787843	1	501936	NS BLESSING	SALARY

As you can see I capture the Trailer Record on Row1 between Columns L & O, but Like I said, I need to capture Contra Records which come before Trailer records and could be an unknown number of Contra Records.

Here is my code so far working with a Buffer Function:

Function BUFFER(ByVal Value As String, Length As Long, Optional Front As Boolean = False, Optional BuffChar As String = " ") As String
If Len(Value) > Length Then Err.Raise 2001, "BUFFER UDF", "Value exceeds length requirements"
If Len(Value) = Length Then
BUFFER = Value
Exit Function
End If
If Front Then
Value = BuffChar & Value
Else
Value = Value & BuffChar
End If
BUFFER = BUFFER(Value, Length, Front, BuffChar)
End Function



Open FPath & fName For Output As File

rowno = 0

rowno = rowno + 1
Range("A" & rowno).Select

If Selection.Value = "" Then
done = True

Else

CountRec = CountRec + 1

' Header Spec
Line = BUFFER(Range("A" & rowno).Text, 1)
Line = Line & BUFFER(Range("B" & rowno).Text, 4)
Line = Line & BUFFER(Range("C" & rowno).Text, 40)
Line = Line & BUFFER(Range("D" & rowno).Text, 8)
Line = Line & BUFFER(Range("E" & rowno).Text, 1) & " "
Line = Line & BUFFER(Range("F" & rowno).Text, 15, False)
Line = Line & BUFFER(Range("G" & rowno).Text, 1)
Line = Line & BUFFER(Range("H" & rowno).Text, 2)
Line = Line & BUFFER(Range("I" & rowno).Text, 1) & " "
Line = Line & BUFFER(Range("J" & rowno).Text, 2)
Line = Line & BUFFER(Range("K" & rowno).Text, 4)
Print #File, Line
End If

CountRec = 0
TAmount = 0

rowno = 1

Do Until done

rowno = rowno + 1
Range("B" & rowno).Select

If Selection.Value = "" Then
done = True

Exit Do
Else
'check if the value is zero
Range("G" & rowno).Select
If Val(Selection.Value) = 0 Then 'NOTHING

Else

CountRec = CountRec + 1

' Detail Spec
Line = BUFFER(Range("A" & rowno).Text, 1)
Line = Line & BUFFER(Range("B" & rowno).Text, 4)
Line = Line & BUFFER(Range("C" & rowno).Text, 6, True, "0")
Line = Line & BUFFER(Range("D" & rowno).Text, 7, True, "0")
Line = Line & BUFFER(Range("E" & rowno).Text, 19, True, "0") & " "
Line = Line & BUFFER(Range("F" & rowno).Text, 1)
Line = Line & BUFFER(Range("G" & rowno).Text, 11, True, "0")
Line = Line & BUFFER(Range("H" & rowno).Text, 20) & " "
Line = Line & BUFFER(Range("I" & rowno).Text, 15, False)

Print #File, Line
End If
End If

Loop

CountRec = 0
TAmount = 0

rowno = 0

rowno = rowno + 1
Range("L" & rowno).Select

CountRec = CountRec + 1

' Trailer spec
Line = BUFFER(Range("L" & rowno).Text, 1)
Line = Line & BUFFER(Range("M" & rowno).Text, 4)
Line = Line & BUFFER(Range("N" & rowno).Text, 1) & " "
Line = Line & BUFFER(Range("O" & rowno).Text, 13, True, "0")

Print #File, Line

Close File



If anyone can help please?