Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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)) |
#3
|
|||
|
|||
Thanks for your quick reply
|
#4
|
|||
|
|||
That works, Much appreciated.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I convert an excel file to a flat text file | Excel Discussion (Misc queries) | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) | |||
Excel File in Use Notification Missing?? | Excel Discussion (Misc queries) | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) |