Get rid of carriage return when outputting to .txt file

[snejsnej]

Hi there,

I have a macro that does a pretty good job at outputting cell values in one column to a text file, with the values delimited by a semi-colon.

However, the values all have a hard return such that each value is on another line in the txt file. How can I get it so that all values are one one line instead?

Thanks!

Here's the macro:

Sub CreateCSV()

Dim rCell As Range
Dim rRow As Range
Dim sOutput As String
Dim sFname As String, lFnum As Long

'Open a text file to write
sFname = "H:\My Documents\From Derek\Test.txt"
lFnum = FreeFile

Open sFname For Output As lFnum
'Loop through the rows'
For Each rRow In ActiveSheet.UsedRange.Rows
'Loop through the cells in the rows'
For Each rCell In rRow.Cells
If rCell.Value <> Empty Then
sOutput = sOutput & rCell.Value & ";"
End If
Next rCell
'remove the last comma'
'sOutput = Left(sOutput, Len(sOutput) - 1)

'write to the file and reinitialize the variables'
Print #lFnum, sOutput
sOutput = ""
Next rRow

'Close the file'
Close lFnum

End Sub

 

[macropod]

Hi snejsnej,

Try:
Print #lFnum, Replace(sOutput, vbCr, "")


Cheers
[MS MVP - Word]

 

[snejsnej]

Hi macropod,
Sorry, no luck with that one. Appreciate the stab at it though.
J

 

[macropod]

Hi snejsnej,

In that case, try:
Print #lFnum, Replace(sOutput, vbCrLf, "")


Cheers
[MS MVP - Word]

 

[snejsnej]

Hi Macropod,
I actually gave that a shot after your first suggestion... didn't work either though.
Thanks again,
J

 

[PHV]

What about this ?

Print #lFnum, Replace(Replace(sOutput, vbLf, ""), vbCr, "")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[adalger]

Print #lFnum, sOutput;

TMTOWDI - it's not just for Perl any more

 

[snejsnej]

Hi everyone,

adalger's suggestion did the trick... thanks!

J