如何使用VBA MS excel将文本文件的数据附加到另一个文本文件

I have an Excel macro that look for a particular string in sheet, and then writes to a txt file if this strning is found.

我有一个Excel宏，在工作表中查找特定的字符串，然后写入一个txt文件，如果找到此strning。

My problem is that the txt file values are overwriten, but I want to append to this file.

我的问题是txt文件值被覆盖，但我想附加到此文件。

How I can fix this?

我怎么解决这个问题？

My code is:

我的代码是：

Option Explicit

Sub MACRO()
    Dim ruta As String
    Dim fi As Long
    Dim pos As Integer
    Dim Sht As Worksheet
    Dim cell As Object

    fi = FreeFile
    ruta = "C:\Users\PE0223\Desktop\Ficheros_Con_Links.txt"
    Set Sht = ThisWorkbook.ActiveSheet

    On Error GoTo Err
    Open ruta For Output As #fi
    On Error GoTo 0
    'Application.DisplayAlerts = False
    For Each cell In Sht.UsedRange.Cells
        pos = InStr(cell.Formula, "C:\")
        If pos <> 0 Then
            Print #fi, ActiveWorkbook.Path & "\" & ThisWorkbook.Name
            Exit For
        End If
    Next

    Close #fi
    Exit Sub

Err:
    Close #fi
End Sub

Thanks!

谢谢！

2 个解决方案

#1

Try changing the line Open ruta For Output As #fi to

尝试更改行打开ruta For Output As #fi to

Open ruta For Append As #fi

This should append data to the text file instead of overwriting it.

这应该将数据附加到文本文件而不是覆盖它。

#2

You could improve your overall code significantly by using Find rather than looping through each cell. Something like:

通过使用“查找”而不是循环遍历每个单元格，可以显着提高整体代码。就像是：

Sub FastDFind()
Dim rng1 As Range
Dim ruta As String
Dim fi As Long

fi = FreeFile
ruta = "C:\Users\PE0223\Desktop\Ficheros_Con_Links.txt"
Open ruta For Append As #fi

Set rng1 = Cells.Find("C:\", , xlFormulas, xlPart)
If Not rng1 Is Nothing Then
    MsgBox "value found", vbInformation
    Print #fi, ActiveWorkbook.Path & "\" & ThisWorkbook.Name
    Close #fi
End If
End Sub

#1