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

Saving XL ranges as Fixed Field Length Text - Leading spaces
 

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

Saving XL ranges as Fixed Field Length Text - Leading spaces
 

Thanks Dave, I'll try it out tomorrow.
Allan
"Dave Peterson" wrote in message
...
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