Excel VBA writing an empty row at the end when saving a text file or a csv with more than one rows?

There is a solution.

When saving a text file or a CSV in VBA, an extra empty row can be added at the end of the file if the file contains more than one line. This can cause issues when importing the file into other systems, as some systems may not recognize the extra empty row and produce errors.

To solve this issue, we can use the WriteToFile function provided in the code. This function uses the FileSystemObject to create a text file and write data to it.

The function takes two parameters: filename, which is the path and name of the file to be created or overwritten, and fileData, which is the data to be written to the file.

The fileData parameter should contain the text to be written to the file, with each line separated by a carriage return and line feed (vbCrLf) character. This is necessary to ensure that the lines are correctly separated in the output file.

To prevent an extra empty row from being added at the end of the file, the WriteToFile function uses a loop to write each line of fileData to the file using the WriteLine method of the file object. However, for the last line of fileData, the Write method is used instead of the WriteLine method to write the line without adding a carriage return and line feed character at the end. This ensures that the file ends with the last line of text, without an extra empty row.

The On Error GoTo statement in the function allows for error handling, in case an error occurs during the file writing process.

Overall, using the WriteToFile function can help prevent the issue of an extra empty row being added to text files or CSVs when saving them in VBA. By ensuring that the files are correctly formatted, this can also help prevent errors when importing the files into other systems.

Function WriteToFile(ByVal filename As String, ByVal fileData As String) As Boolean
    Dim fso As Object
    Dim file As Object
    Dim lines As Variant
    Dim i As Integer
    On Error GoTo ErrorHandler
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set file = fso.CreateTextFile(filename, True)
    lines = Split(fileData, vbCrLf)
    For i = 0 To UBound(lines) - 1
        file.WriteLine lines(i)
    Next i
    file.Write lines(UBound(lines))
    file.Close
    WriteToFile = True
    Exit Function
ErrorHandler:
    WriteToFile = False
End Function