How to remove the empty line that excel makes when creating a csv file using vba

Question

As some of you probably know, excel creates an empty line at the end of CSV files. I'm looking for a solution that can remove/delete this line because I want to upload the CSV file to a different program, which can't handle this empty line.

First I thought it was the way I created the CSV file, but after spending hours searching for a solution, I found out that it's a bug.

Does anybody have a solution to this problem, removing the last line in a CSV file using VBA?

Answer 1

Try calling this Sub to kill the last line of the csv-file. You have to insert the path into the code:

Sub KillLastLine()
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim filecontent As String
    Dim myFile As File

    Set myFile = fso.GetFile("YourCSVPathHere")
    Set ts = myFile.OpenAsTextStream(ForReading)
    While Not ts.AtEndOfStream
        filecontent = filecontent & ts.ReadLine & vbCrLf
    Wend
    Set ts = myFile.OpenAsTextStream(ForWriting)
    ts.Write Left(filecontent, Len(filecontent) - 1)
    ts.Close
End Sub