Save CSV without carriage return (CR)

cutiedanger

New Member
Joined
Sep 17, 2014
Messages
2
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.

Code:
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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

cutiedanger

New Member
Joined
Sep 17, 2014
Messages
2
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.

Code:
dos2unix test-file.txt
 

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,100
Couple Options:

Code:
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
Code:
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_

Well-known Member
Joined
Nov 9, 2009
Messages
567
or
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,750
Messages
5,446,271
Members
405,393
Latest member
sully361

This Week's Hot Topics

Top