Saving XL ranges as Fixed Field Length Text - Leading spaces

  • Thread starter Thread starter AS
  • Start date Start date
A

AS

Courtesy of J E McGimpsey http://www.mcgimpsey.com/excel/textfiles.html
The following macro produces a text file with predefined field lengths, each
field is padded with trailing spaces.
I'm trying without success to modify this to pad with leading spaces, could
anyone help me out?
Thanks

Public Sub FixedFieldTextFile()
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(20, 10, 15, 4)
nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & 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
 
I fiddled with this line:

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

and changed it to:

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

It looked like it worked ok.
 
Thanks Dave, I'll try it out tomorrow.
Allan
Dave Peterson said:
I fiddled with this line:

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

and changed it to:

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

It looked like it worked ok.
Click to expand...
 
Back
Top