Saving XL ranges as Fixed Field Length Text - Leading spaces

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

Hi,

You needed to change the line in Blue from Left to Right in order to pad with Leading Spaces.

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 & Right(String(vFieldArray(i), PAD) & (.Offset(0, i).Text , _ vFieldArray(i))
Next i
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub

No problem,
Leith Ross

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.

AS wrote:
>
> 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

--

Dave Peterson

Thanks Dave, I'll try it out tomorrow.
Allan
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>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.
>
> AS wrote:
>>
>> 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
>
> --
>
> Dave Peterson