Reputation: 125
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
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
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