Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Macro to convert Excel file to Pipe Delimited Text
#1
Here is a Macro to convert an excel file to save as a pipe delimited text that i found online. It works perfectly except for the fact that it stops at column T. I need it to continue to Column X but it does not. I have entered the character spaces needed up to column X but it will not save the file past column T.

Any help would be appreciated.

Public Sub FixedFieldTextFile()
Const DELIMITER As String = "|"
Const PAD As String = " "
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(6, 2, 1, 12, 14, 10, 100, 100, 100, 100, 8, 8, 1, 9, 30, 5, 5, 5, 3, 100)
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


Forum Jump:


Users browsing this thread: 2 Guest(s)