代码之家 › 专栏 › 技术社区 › Shai Rado

将Excel范围导出到时获取额外的空行。txt文件

export-to-text export-to-csv excel vba

Shai Rado · 技术社区 · 7 年前

我正在尝试将Excel范围复制到。txt文件。

导出成功,但有一个例外,它添加了一个“ 额外的 “末尾为空行。

我已经阅读并测试了许多解决方案所以 (和其他网站),但仍然没有任何成功。

我的密码 (相关部分)

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

我的 StockSht (工作表对象)和 LastRow 正确定义,并获取其值。

导出结束的屏幕截图。txt文件

2 回复 | 直到 6 年前

Robin Mackenzie 7 年前

您可以在中使用分号 Print 语句控制插入点(即防止最后一行换行)。

MSDN页面上的相关位:

使用分号将插入点放置在显示的最后一个字符之后。

我测试了此代码:

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

因此,我会像下面这样更新您的代码(未测试):

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

Shai Rado 7 年前

尝试使用 ; 在最后一个打印行。

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

推荐文章