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

Question

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

Why not just save the sheet as a txt file and skip the line by line print? — Scott Craner, Dec 08 '17 at 20:02
Sorry I could not help. I assume you stepped through the code to ensure that the lastrow is the row you expect? — Scott Craner, Dec 08 '17 at 20:16
@ScottCraner yes, too many simulations. Also, tested more than a few solutions on SO and other sites, but still nothing — Shai Rado, Dec 08 '17 at 20:18

Robin Mackenzie · Answer 1 · 2017-12-08T22:47:36.813

You can use a semi-colon in the Print statement to control the insertion point (i.e. prevent the line-feed on the last line).

The relevant bit on the MSDN page:

Use a semicolon to position the insertion point immediately after the last character displayed.

I tested this code:

Sub PrintTest()

    Dim lng As Long

    Open "C:\foo3.txt" For Output As #1

    For lng = 1 To 10
        If lng < 10 Then
            Print #1, "foo" & lng
        Else
            Print #1, "foo" & lng; '<-- semi-colon prevents the newline
        End If
    Next lng

    Close #1

End Sub

So I would update your code like below (not tested):

' === 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

        '--- new bit: check for i against LastRow and add the semicolon on last row
        If i <> LastRow Then
            Print #1, lineText
        Else
            Print #1, lineText; '<-- semi colon keeps insertion point at end of line
        End If


        lineText = ""
    Next i
End With
Close #1

Yep but when I was writing it you completely edited your original answer that you had (temporarily) deleted ! So I thought about deleting mine but I think my answer is better than yours because I give a tested example of the behaviour of `Print` with semi-colon as well as a fix for Shai's problem. So I am happy to leave mine as the minimal example is useful beyond the scope of the OP question :) — Robin Mackenzie, Dec 08 '17 at 22:44

score 2 · Accepted Answer · edited Dec 08 '17 at 20:44

2

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

edited Dec 08 '17 at 20:44

Shai Rado

33,032
6
29
51

answered Dec 08 '17 at 20:06

Scott Craner

148,073
10
49
81

Sorry, same result, getting an extra empty line at the end. Did you get to test it, I only faced it now because the SAP "Screams" when he tries to read an empty last row – Shai Rado Dec 08 '17 at 20:11
I there a way to open a .txt file, go to the last line and press {backspace} ? – Shai Rado Dec 08 '17 at 20:12
to be honest I only use this to easily transfer data from my excel to existing SQL tables using Bulk Insert, which this works well. – Scott Craner Dec 08 '17 at 20:12
I would use powershell to do that not vba, but that requires another step. – Scott Craner Dec 08 '17 at 20:13
Go%# da you saved my ass here, thanks :) Diner is on me next time I get to the states – Shai Rado Dec 08 '17 at 20:47
1

I might know everything, but I can google with the best of them. – Scott Craner Dec 08 '17 at 20:47

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

2 Answers2