my macro creates a carriage return at the end of my csv .txt output and can't figure out why

Question

My excel file has 3 sheets, one that contains a mass amount of static data (we'll call this Build Status), one that accepts data input (we'll call this Input) and one that returns data from Build Status sheet using IF and VLOOKUP functions based on values from Input sheet (we'll call this sheet Output). My Output sheet has static headers and my macro creates a csv from the Output sheet (macro tied to a button). For some reason I end up with a carriage return at the end of my .txt file (csv saved as .txt) and I have to delete it in order for the HMI of another program to accept the upload format. Any help on getting rid of the return or why it's being created would be appreciated.

Whether outputting as .csv or .txt (in csv format), the carriage return is there when viewing in a text editor.

Option Explicit

Sub CreateCSV()
    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet, wsO As Worksheet
    Dim jLastRow As Long
    Dim LDate As String

    '~~> Set a format for current date
    LDate = Format(Date, "mm-dd-yyyy")

    '~~> Source/Input Workbook
    Set wbI = ThisWorkbook

    '~~> Set the relevant sheet from where you want to copy
    Set wsI = wbI.Sheets("OUTPUT")

    '~~> Sets an integer value for the last row of data being copied
    jLastRow = wsI.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    '~~> Destination/Output Workbook
    Set wbO = Workbooks.Add

    With wbO
        '~~> Set the relevant sheet to paste to
        Set wsO = wbO.Sheets("Sheet1")

        '~~> Copy the range
        wsI.Range("A1:Z" & jLastRow).Copy

        '~~> Paste it in Cell A1.
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValues

        '~~>. Save the file
        .SaveAs Filename:="\\TestFolder\TestRecipe" & LDate & ".txt", FileFormat:=xlCSV

        '~~> Clear Clipboard
        Application.CutCopyMode = False
    End With
End Sub

Expected: Creates comma separated values in .txt file named TestRecipe(Date) that works with third party software.

Actual: File creation, name and format work but a carriage return is at the end of the file which prevents it from uploading into third party software. As soon as I delete this manually, it uploads fine.

Instead of using `.SaveAs FileFormat:=xlCSV`, you could try [converting the range to a CSV string](https://stackoverflow.com/a/39781372/4717755), then deleting the very last carriage return and then write the resulting string to a text file. — PeterT, Sep 04 '19 at 13:53
I did not attempt this due to finding the true issue at hand but it may be useful for someone else. Thank you! — Metharious, Sep 13 '19 at 16:01

score 0 · Accepted Answer · edited Nov 03 '19 at 20:24

0

CRs and LFs might be added on purpose. Anyhow if it's like that this should cure it :)

function remove_lastwhitespace_if_any (S as string ,optional tester as string = vbtab & vbcr & vblf & " ") as string 
    if len(s) =0 then exit function

    while instr(tester,right(s,1) ) > 0 
       S=left(s,len(s)-1)
    wend

    remove_lastwhitespace_if_any=s
end function

edited Nov 03 '19 at 20:24

halfer

19,824
17
99
186

answered Sep 04 '19 at 15:17

Thomas Ludewig

696
9
17

While I did use this solution, I found the real error at hand which was the HMI would not accept the upload if the file was open at the same time. My macro left the .csv file open at the end of the cycle so the reason why I thought deleting the carriage return worked is because I would manually delete it, save and close. Where as if I just attempted to upload after my macro created the file, it wouldn't because it was still open. Thank you for your response, and I apologize for the delay in mine. – Metharious Sep 13 '19 at 16:06
:) No problem - better late then never ;) I am glad you find your tricky bug :) – Thomas Ludewig Sep 13 '19 at 16:37

my macro creates a carriage return at the end of my csv .txt output and can't figure out why

1 Answers1