CODEWITHSUNDEEP
excelvba
Mr.Beto
Mr.Beto

Reputation: 125

How to write in txt file with vba

I have a macro in VBA searching some string in cells and after write in the txt file the path of the excel file, but it doesn't work well all, the write in file txt and the search string. How I can do this.

Thanks!

My code is :

Attribute VB_Name = "Buscar_String"

Sub MACRO()
    Dim ruta As String = "C:\Ficheros_Con_Links.txt"
    Dim fi As FileInfo = New FileInfo(ruta)
    Dim sw As StreamWriter
    Dim Sht As Worksheet
    Application.DisplayAlerts = False
    For Each cell in Sht.Cells 
        If strComp(cell, "T:\", 1) = 0 then
            If File.Exists(ruta) = False Then
                sw = File.CreateText(ruta)
            End If
        sw.WriteLine (ActiveWorkbook.Path & "\" & ThisWorkbook.Name)
        sw.Flush()
        sw.Close()
        End If
    Next
End Sub

Now, I change my code, and work well

   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
        End If
    Next

    Close #fi
    Exit Sub

Err:
    Close #fi
End Sub

Upvotes: 0

Views: 8037

Answers (1)

Lukas2
Lukas2

Reputation: 320

Your code seems to be vb.net, not vba.

In case you need solution in vba, here is code:

Option Explicit

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

    fi = FreeFile
    ruta = "D:\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
        If StrComp(cell, "T:\", 1) = 0 Then
            Print #fi, ActiveWorkbook.Path & "\" & ThisWorkbook.Name
        End If
    Next

    Close #fi
    Exit Sub

Err:
    Close #fi
End Sub

Update:

As discussed in comments, see below changed code - this should find text even if it is in longer string. 

Sub MACRO()
    Dim ruta As String
    Dim fi As Long
    Dim Sht As Worksheet
    Dim cell As Range

    fi = FreeFile
    ruta = "D:\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
         If InStr(cell.Value, "T:\") > 0 Then
            Print #fi, ActiveWorkbook.Path & "\" & ThisWorkbook.Name
        End If
    Next

    Close #fi
    Exit Sub

Err:
    Close #fi
End Sub

Upvotes: 2

Related Questions