CODEWITHSUNDEEP
excelvbatextreplace
Pegaso
Pegaso

Reputation: 135

How to find specific text in text file, and add additional text in the following line in excel VBA

I need to open a txt file, find an specific string, write several lines right below it, save the resulting file and close it. This is what I have so far:

Sub EditMyTXT()

Dim FileNum As Integer
Dim DataLine As String

FileNum = FreeFile()
Open "E:\Host.txt" For Input As #FileNum

While Not EOF(FileNum)
    Line Input #FileNum, DataLine
        If DataLine = "TextToFind" Then
             move to the next line
             write text "TextToWriteBelow TextToFind"
             move to the next line
             write text "MoreTextToWriteBelow"
        Else: End If
Wend

 Save FileNum
 Close FileNum

End Sub

I can't find a way to open a txt file in a mode that would allow me to read an write to it. Any ideas, suggestions?

Upvotes: 0

Views: 954

Answers (1)

Storax
Storax

Reputation: 12167

You could also read the text file into a string variable, change that and write it back like that

Sub EditMyTXT_Full()

    Dim FileNum As Integer
    Dim DataLine As String
    Dim fileName As String

    fileName = "D:\TEMP\HOST.TXT"

    FileNum = FreeFile()
    Open fileName For Input As #FileNum

    Dim textData As String
    ' read the complete file into textdata
    textData = Input$(LOF(FileNum), FileNum)
    Close #FileNum

    Dim searchLine As String
    Dim newLines As String

    searchLine = "Test"
    newLines = "New Line 1" & vbCrLf & "New Line 2"

    Dim vdat As Variant
    vdat = Split(textData, vbCrLf)

    Dim i As Long
    ' This is the loop through the lines but completely in memory
    For i = LBound(vdat) To UBound(vdat)
        If vdat(i) = searchLine Then
            vdat(i) = vdat(i) & vbCrLf & newLines
        End If
    Next i

    textData = Join(vdat, vbCrLf)

    FileNum = FreeFile()
    Open fileName For Binary Access Write As #FileNum
    ' write the text back in one step
    Put #FileNum, , textData
    Close #FileNum

End Sub

Upvotes: 2

Related Questions