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?

EngJon · Accepted Answer · 2016-02-03 15:52:57Z

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

edited Feb 3, 2016 at 15:52

answered Feb 3, 2016 at 14:50

EngJon

9979 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

K.Olesen Over a year ago

Thanks for your answer @EngJon. Your code are converting 10 lines into one line. The csv file contain approx 10 lines plus the last line which is empty and have to be deleted. I'm sorry if my question wasn't clear enough.

EngJon Over a year ago

Hmm, it shouldn't do this. Chr(10) should add line breaks, so the original lines would persist. Please Try vbCrLf instead of Chr(10) and tell me if it works.

EngJon Over a year ago

No problem, I'll edit my answer accordingly. Feel free to accept it as answer then ;)

Ricardo Navas · Accepted Answer · 2016-09-29 19:29:04Z

Sub ZUtil_TextFile_FindReplace(FilePath As String, strOld As String, strNew As String)
'PURPOSE: Modify Contents of a text file using Find/Replace
'SOURCE: www.TheSpreadsheetGuru.com
Dim TextFile As Integer
Dim FileContent As String
'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile
'Open the text file in a Read State
  Open FilePath For Input As TextFile
'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)
'Clost Text File
  Close TextFile
'Find/Replace
  FileContent = Replace(FileContent, strOld, strNew)
  FileContent = Replace(FileContent, "^(?:[\t ]*(?:\r?\n|\r))+", "")
  If Right(FileContent, 2) = Chr(13) & Chr(10) Then
    FileContent = Left(FileContent, Len(FileContent) - 2)
  End If
'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile
'Open the text file in a Write State
  Open FilePath For Output As TextFile
'Write New Text data to file
  Print #TextFile, FileContent
'Close Text File
  Close TextFile
  'MsgBox "ZUtil_TextFile_FindReplace TERMINADO"
End Sub

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related