vbaexcelexport-to-csvexport-to-text

Getting an Extra Empty line when exporting Excel Range to .txt file


I am trying to copy an Excel range to a .txt file.

The export is successful, with one exception, It adds one "extra" empty line at the end.

I've read and tests many of the solution on SO (and other sites), but still without any success.

My Code (relevant part)

' === Export to the .txt file ===
Dim TxtFileName As String, lineText As String

TxtFileName = ThisWorkbook.Path & "\Inv_" & Format(Date, "yyyymmdd") & ".txt"

Open TxtFileName For Output As #1
With StockSht
    For i = 1 To LastRow
        For j = 1 To 3
            If j = 3 Then
                lineText = lineText & .Cells(i, j).Value2
            Else ' j = 1 or 2
                lineText = lineText & .Cells(i, j).Value2 & vbTab
            End If
        Next j
        Print #1, lineText
        lineText = ""
    Next i
End With
Close #1

My StockSht (worksheet object) and LastRow are defined correctly, and getting their values.

Screen-shot of the end of the exported .txt file

enter image description here


Solution

  • Try using a ; on the last print line.

    ' === Export to the .txt file ===
    Dim TxtFileName As String, lineText As String
    
    TxtFileName = ThisWorkbook.Path & "\Inv_" & Format(Date, "yyyymmdd") & ".txt"
    
    Open TxtFileName For Output As #1
    With StockSht
        For i = 1 To LastRow
            For j = 1 To 3
                If j = 3 Then
                    lineText = lineText & .Cells(i, j).Value2
                Else ' j = 1 or 2
                    lineText = lineText & .Cells(i, j).Value2 & vbTab
                End If
            Next j
            If i = LastRow Then
                Print #1, lineText;
            Else
                Print #1, lineText
            End if
            lineText = ""
        Next i
    End With
    Close #1