Pipe Delimited Macro Help

[tuntex]

Hi All,

I have a macro that exports data in a | delimited format. All is working well except I can't get the macro to finish with a trailing pipe.

IE field1|field2|field3|

I have even put the final pipe in the last field, it still drops it. Does anyone have a pointer to a solution. I can't change the operating system delimiter as several things rely on comma delimiters.

Below is the code im using.

Thanks for any ideas.

Sub ExportPipeDelimited()
Dim vFF As Long, vSaveName As String
Dim i As Long, j As Long, l As Long
Dim CLLs() As String, Rws, vTempStr As String

'Get Exported File Name
vSaveName = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:= _
"Pipe-delimited text,*.txt,All Files,*.*", Title:="Export Pipe Delimited ...")
If LCase(vSaveName) = "false" Then Exit Sub 'hit cancel

'Create array with pipe-delimited sheet data
Rws = ActiveSheet.UsedRange
ReDim CLLs(UBound(Rws, 1) - LBound(Rws, 1))
For i = LBound(Rws, 1) To UBound(Rws, 1)
vTempStr = ""
For j = LBound(Rws, 2) To UBound(Rws, 2)
vTempStr = vTempStr & "|" & Rws(i, j)
Next j
For l = Len(vTempStr) To 2 Step -1
If Mid$(vTempStr, l, 1) <> "|" Then Exit For
Next
CLLs(i - LBound(Rws, 1)) = Mid$(vTempStr, 2, l - 1)
Next i

'Transfer array contents to text file
vFF = FreeFile
Open vSaveName For Output As #vFF
For i = 0 To UBound(CLLs) - 1
Print #vFF, CLLs(i)
Next i
Print #vFF, CLLs(UBound(CLLs))
Close #vFF
End Sub

 

[sunnyland]

Hello there,


I have a question for you: why to you need it to finished with a pipe character if the field delimiter is pipe, it doens't make sense [to me] apart if if the last field is an empty field.

And I assume, if you want to have it in this format is to be able to import it in another program.

Am I right to assume the following???

Regard
Francoise

 

[tuntex]

Correct,

Unfortunately I don't have any control of the final destination of the file.

Thats what has been requested so thats what I have to provide.

 

[Legacy 98055]

I see no place in your code that you are adding a trailing (ending) pipe?

 

[tuntex]

Sorry, i'm not very good with vba. Could you give me any pointers how to put a trailing | in ?

thanks

Mick

 

[sunnyland]

Still thinking of your problem, but haven't much time yet.

I can see with this line, you are adding a pipe character.

vTempStr = vTempStr & "|" & Rws(i, j)

Will get back to you in a few days.

Francoise

 

[Legacy 98055]

Still thinking of your problem, but haven't much time yet.

I can see with this line, you are adding a pipe character.

vTempStr = vTempStr & "|" & Rws(i, j)

Will get back to you in a few days.

Francoise

That's inserted, not trailing...
Just use this...

Print #vFF, CLLs(UBound(CLLs)) & "|"

 

[jmyers1643]

anybody know how to modify the script posted by the poster in order to ignore the top row?