解决Visual Basic导出TXT文件时出现多余空白行的问题

你遇到的多余空白行，是因为代码里每循环一行数据就执行一次tx.WriteLine——这个语句会在最后一行数据之后也添加一个换行符，导致文件末尾多出空白行。

下面提供两种可行的修改方案：

方案1：调整换行逻辑（仅非最后一行添加换行） #

直接修改循环内的换行判断，只在当前行不是最后一行时写入换行：

Sub Creatxt()

Dim NombreArchivo, RutaArchivo As String
Dim obj As FileSystemObject
Dim tx As Scripting.TextStream
Dim ht As Worksheet
Dim i, j, nFilas, nColumnas As Integer

NombreArchivo = "OPG"
RutaArchivo = ActiveWorkbook.Path & "\" & NombreArchivo & ".txt"

Set obj = New FileSystemObject
Set tx = obj.CreateTextFile(RutaArchivo)
Set ht = Worksheets("Datos")

nFilas = ht.Cells(Rows.Count, 1).End(xlUp).Row
nFilas = nFilas - 7
nColumnas = ht.Range("A8", ht.Range("A8").End(xlToRight)).Cells.Count

For i = 1 To nFilas
    For j = 1 To nColumnas
      tx.Write ht.Cells(i + 7, j)
      If j < nColumnas Then tx.Write vbTab
    Next j
    ' 仅非最后一行添加换行
    If i < nFilas Then tx.WriteLine
Next i
tx.Close ' 关闭文本流，确保内容完全写入
End Sub

方案2：先拼接所有内容再批量写入（更高效） #

先把所有行的内容用换行符拼接好，最后一次性写入文件，从根源避免多余换行：

Sub Creatxt()

Dim NombreArchivo, RutaArchivo As String
Dim obj As FileSystemObject
Dim tx As Scripting.TextStream
Dim ht As Worksheet
Dim i, j, nFilas, nColumnas As Integer
Dim lineContent As String
Dim allContent As String

NombreArchivo = "OPG"
RutaArchivo = ActiveWorkbook.Path & "\" & NombreArchivo & ".txt"

Set obj = New FileSystemObject
Set tx = obj.CreateTextFile(RutaArchivo)
Set ht = Worksheets("Datos")

nFilas = ht.Cells(Rows.Count, 1).End(xlUp).Row
nFilas = nFilas - 7
nColumnas = ht.Range("A8", ht.Range("A8").End(xlToRight)).Cells.Count

allContent = ""
For i = 1 To nFilas
    lineContent = ""
    For j = 1 To nColumnas
      lineContent = lineContent & ht.Cells(i + 7, j)
      If j < nColumnas Then lineContent = lineContent & vbTab
    Next j
    ' 拼接行内容，最后一行不追加换行
    If i = 1 Then
        allContent = lineContent
    Else
        allContent = allContent & vbCrLf & lineContent
    End If
Next i

tx.Write allContent
tx.Close
End Sub

内容的提问来源于stack exchange，提问作者Neyber Lopez