Save CSV without carriage return (CR)

[cutiedanger]

I am attempting to create a comma-separated text file that will be used to import data into a third-party program. The program requires that there not be carriage returns (CR) at the end of each line.

I have attempted to save as CSV (Comma delimited) (*.csv), CSV (Macintosh) (*.csv), and CSV (MS-DOS) (*.csv), but all are adding carriage returns at the end of each line.

I found this macro, but it does not seem to work either.

Sub Save_As_CSV_No_CR()
    Range("CJ1").Select

    Dim myRng As Range
    Set myRng = ActiveCell.EntireColumn
    myRng.Replace What:=vbCr, Replacement:="", _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Set myRng = Nothing

    ChDir "C:\Users\cutiedanger\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\cutiedanger\Desktop\test-file.txt", FileFormat:=xlCSV, _
        CreateBackup:=False
End Sub

Is there a way to save the file as a comma delimited text file without carriage returns?

 

[cutiedanger]

While I'd like to save it in Excel without CRs, this file will ultimately end up being processed with batch. I can use Dos2Unix to convert the file to Unix format.

dos2unix test-file.txt

 

[rpaulson]

Couple Options:

Sub Creat_Txt_File()

myfile = "c:\test.csv"
If Dir(myfile) <> "" Then Kill myfile ' deletes file if it exists

For r = 1 To Range("A65536").End(xlUp).Row
    lcol = Cells(r, 256).End(xlToLeft).Column
    For c = 1 To lcol
    delim = ""
    If c < lcol Then delim = ","
    data = data & Cells(r, c) & delim
    Next c
Open myfile For Append As #1
Print #1, data
Close #1
data = ""
Next r

or

End Sub
Sub outputtotextfile()
wline = ""

For r = 1 To Range("A65536").End(xlUp).Row
    lcol = Cells(r, 256).End(xlToLeft).Column
    wline = wline & Join(Application.Transpose(Application.Transpose(Range("A" & r).Resize(, lcol))), ",") & vbNewLine
Next r
    
    

    Open "C:\test2.txt" For Output As #1 'Replaces existing file
    Print #1, wline
    Close #1

End Sub

 

[snb_]

or

Sub M_snb()
   ThisWorkbook.Sheets(1).UsedRange.Copy
  
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
          .GetFromClipboard
          c00 = Replace(Replace(.GetText, vbCr, ""), vbTab, ",")
    End With
    CreateObject("scripting.filesystemobject").createtextfile("G:\OF\example.txt").write c00
End Sub