Need help with creating a text file via excel

[ANDRE.TASSEL]

I'm trying to create a text file that will keep the fields a constant
length, although the data within them varies length.

The following VB almost does what I want EXCEPT that the padding in
fields 4 to 31 (set at 6 characters) is trailing the actual data and I
want it to be leading (eg field 4 may be 693, but it comes out as
693000).

if the 3 trailing zero's were at the front, then the application I'm
sending it to (AS400) would strip them leaving just the 693.

Const DELIMITER As String = "" 'Normally none
Const PAD As String = " " 'or other character
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String

'vFieldArray contains field lengths, in characters, from field
1 to N
vFieldArray = Array(11, 4, 2, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6)
nFileNum = FreeFile
Open "C:\planning\MPS.TXT" For Output As #nFileNum
For Each myRecord In Range("C7:C" & Range("C" &
rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text &
String(vFieldArray(i), PAD), vFieldArray(i))
Next i
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty

End With

Next myRecord
Close #nFileNum
End Sub


Any help is appreciated

 

[JE McGimpsey]

One way:

sOut = sOut & DELIMITER & Right(String(vFieldArray(i), PAD) & _
.Offset(0, i), vFieldArray(i))


Left(.Offset(0, i).Text &
String(vFieldArray(i), PAD), vFieldArray(i))

 

[ANDRE.TASSEL]

Thanks for your quick reply

 

[ANDRE.TASSEL]

That works, Much appreciated.