Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fixed length string of text | Excel Discussion (Misc queries) | |||
Text to column fixed length | Excel Discussion (Misc queries) | |||
Fixed Length, Text Formatted | Excel Discussion (Misc queries) | |||
Fixed length text file | Excel Discussion (Misc queries) | |||
Export file to CSV delimited with fixed field length | Excel Discussion (Misc queries) |