我怎么能避免行馈线在文本文件中，当我写的内容从excel表

Question

我写的从excel表格到文本文件的VBA代码如下:

ActiveWorkbook.Sheets("Sheet1").Activate
With ActiveSheet
    .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Select
End With
Dim strPath As String
Dim FSO As Object
Dim oFile As Object
Set oFile = Nothing
Dim c As Range
Dim linefeed_remover
strPath = "path"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFile = FSO.CreateTextFile(strPath & filesname)
For Each c In Selection
    oFile.Write Application.WorksheetFunction.Clean(c.Value) & vbLf
Next c
oFile.Close
strNameOld = strName & "_Old"
'MsgBox "File Created"
'conn2.Close

谁能帮我解决这个问题?

Answer 1

建议

1. 不要循环遍历一个范围中的单元格。将它们存储在数组中并循环遍历该数组。

2. 使用数组也让我们知道我们正在处理哪个记录。如果我们正在使用最后一条记录，我们可以跳过添加life feed。

Sub Sample()
    Dim strPath As String
    Dim FSO As Object, oFile As Object
    Dim c As Range
    Dim linefeed_remover
    Dim MyAr
    ActiveWorkbook.Sheets("Sheet1").Activate
    With ActiveSheet
        .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Select
    End With
    Set oFile = Nothing
    strPath = "C:UsersSiddharthDesktop"
    filesname = "Sample.Txt"
    '~~> Transfer the entire range in an array
    '~~> For faster performcance
    MyAr = Selection.Value
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = FSO.CreateTextFile(strPath & filesname)
    For i = LBound(MyAr) To UBound(MyAr)
        '~~> Check if it is the last record so that
        '~~> We do not add the linefeed
        If i = UBound(MyAr) Then
            oFile.Write Application.WorksheetFunction.Clean(MyAr(i, 1))
        Else
            oFile.Write Application.WorksheetFunction.Clean(MyAr(i, 1)) & vbNewLine
        End If
    Next i
    oFile.Close
End Sub