CODEWITHSUNDEEP
vba
Min Naing Oo
Min Naing Oo

Reputation: 1095

Remove extra line break written with TextStream.WriteLine()

I have following vba code which write items in the ArrayList to file with TextStream.

Sub WriteListAsCSV(list As Object, filePath As String)
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Set ts = fso.CreateTextFile(filePath, True)
    Dim line As Variant
    For Each line In list
        ts.WriteLine (line)
   Next line
   ts.Close
End Sub

Problem is I'm getting extra linebreak at the end of the file.

I can do like following but I don't want to check at each loop for that single linebreak.

Sub WriteListAsCSV(list As Object, filePath As String)
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Set ts = fso.CreateTextFile(filePath, True)
    Dim line As Variant
    Dim i As Integer
    For i = 0 To list.Count
        line = list(i)
        ts.Write (line)
        'If not last line
        If Not i = list.Count Then
            'Write blankline
            ts.WriteLine()
        End If
   Next
   ts.Close
End Sub

Is there any way to remove one character back like Backspace button in VBA? Or another neat trick to do this?

Upvotes: 1

Views: 1843

Answers (1)

smagnan
smagnan

Reputation: 1267

Idea

My idea was to remove the last charaters corresponding to the linebreak, which are Chr(10) or Chr(13) and sometimes both, using the file length.

How to do

While I was checking about how to use a function I found this: Remove last carriage return file which covers exactly my idea ...
Maybe have a look it's pretty clear.

EDIT (cf @ Siddharth Rout comment)
Modified and commented version of the code (In case of the link dies. I take no credit for this code)

Set objFSO = CreateObject("Scripting.FileSystemObject")   
Set objFile = objFSO.OpenTextFile("path\to\file", 1)     ' -- 1:read

strFile = objFile.ReadAll
objFile.Close

' We check if the two last characters correspond to a linebreak:
If Right(strFile, 2) = vbCrLf Then 
    ' If so, we remove those charaters:
    strFile = Left(strFile, Len(strFile)- 2)
    Set objFile = objFSO.OpenTextFile("path\to\file", 2) ' -- 2:write
    objFile.Write strFile
    objFile.Close
End If

I'm not sure it's the more elegant way to do but it seems to be a pretty valid one. Hope this works for you.

Upvotes: 3

Related Questions