CODEWITHSUNDEEP
excelvbacsvnotepadblank-line
Clemens Ambros
Clemens Ambros

Reputation: 71

CSV file created with VBA has blank row(s) at the end even if cells are empty

I am adding some data in the first column of a worksheet and am using this code

Sub createCSVfile()

Dim xRg As Range
Dim xRow As Range
Dim xCell As Range
Dim xStr As String
Dim xTxt As String
Dim xName As Variant

ThisWorkbook.Worksheets("Tabelle1").Range("A1").Value = "XYZ"
ThisWorkbook.Worksheets("Tabelle1").Range("A2").Value = "XYZ"
ThisWorkbook.Worksheets("Tabelle1").Range("A3").Value = "XYZ"
ThisWorkbook.Worksheets("Tabelle1").Range("A4").Value = "XYZ"
ThisWorkbook.Worksheets("Tabelle1").Range("A5").Value = "XYZ"

Set xRg = ThisWorkbook.Worksheets("Tabelle1").Range("A1:A5")
xName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
Open xName For Output As #1
For Each xRow In xRg.Rows
    xStr = ""
    For Each xCell In xRow.Cells
        xStr = xStr & xCell.Value & Chr(9)
    Next
    While Right(xStr, 1) = Chr(9)
        xStr = Left(xStr, Len(xStr) - 1)
    Wend
    Print #1, xStr
Next
Close #1
If Err = 0 Then MsgBox "csv file saved"

End Sub

to save it as csv and delete all quotation marks based on this tutorial.

When I open the csv with Notepad there always is at least one blank line at the end:

enter image description here

It does not make a difference if I save the file manually - same blank line. I also tried to add something like

ThisWorkbook.Worksheets("Tabelle1").Rows(6).Delete

but it doesn't make a difference because there are no values in the 6th row of the worksheet.

Is there a way to counter this or at least automate deleting the last line in the csv via Notepad?

Upvotes: 1

Views: 1262

Answers (2)

Clemens Ambros
Clemens Ambros

Reputation: 71

as Wolfgang Jacques suggested in the comments, I used this method instead of SaveWorkbookAs

Sub CreateAfile
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\testfile.txt", True)
    a.WriteLine("This is a test.")
    a.Close
End Sub

and for the last entry I used a.Write instead of a.WriteLine

Upvotes: 1

Wolfgang Jacques
Wolfgang Jacques

Reputation: 769

There is no blank row. You got 5 rows, each ended with a CR/LF (Carriage Return and Line Feed). The Text Editor gives you an empty 6th line.

To avoid this you would have to delete the CR/LF from the 5th row but that is not usually done.

It is fine as it is.

Upvotes: 2

Related Questions