Need help with creating a text file via excel
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 |
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)) In article .com, wrote: sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i)) |
Thanks for your quick reply
|
That works, Much appreciated.
|
All times are GMT +1. The time now is 05:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com