Search code examples
excelvbacsvnotepadblank-line

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?

Solution

  • 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