CODEWITHSUNDEEP
excelvba
Mr.Beto
Mr.Beto

Reputation: 125

How I can appending data of text file to another text file using 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.

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

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!

Upvotes: 1

Views: 1049

Answers (2)

brettdj
brettdj

Reputation: 55712

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

Upvotes: 0

Netloh
Netloh

Reputation: 4378

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

Open ruta For Append As #fi

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

Upvotes: 1

Related Questions