How do I Replace a String in a Line of a Text File Using FileSystemObject in VBA?

Question

I am trying to use FileSystemObject methods to find a specific line in a text file, and within that line replace a specific string. I am relatively new to this, as my current code has excel open the text file and replace what I need it to replace then save and close it. This way is no longer an option, as having excel open the text file takes too long and holds up the file.

This is how far I have gotten so far.

-

Sub FindLines()

Const ForReading = 1

Set FSO = CreateObject("Scripting.FileSystemObject")

Set objFSO = FSO.OpenTextFile("C:\Users\Carella Home\Desktop\boomboom.txt", ForReading,     False)

Do Until objFSO.AtEndOfStream = True

    go = objFSO.ReadLine

    If InStr(1, go, "ant", vbTextCompare) > 0 Then

        bo = Replace(go, "t", "wow")

    End If

Loop

objFSO.Close

Set objFSO = FSO.OpenTextFile("C:\Users\Carella Home\Desktop\boomboom.txt", 2)

End Sub

-

The best I can do is open the file up to write, but I have no idea how to find the line and replace it with the line that I need to replace it with.

Please let me know if, in the event that you are willing to help/guide me in the correct direction, you need more information. I have searched a lot and have seen people suggest other ways of doing this. I need to learn how to edit lines this way. Can someone please help me?

Thanks in advance!

-Anthony C.

Solution 1

Not sure if this is the most efficient method but one idea would be to use the CreateTextFile method of the FileSystemObject, to create another file you can write to.

I've tested this on a small file and appears to be working as expected.

Modified after answer accepted to avoid .ReadLine and .WriteLine loops

Sub FindLines()
'Declare ALL of your variables :)
Const ForReading = 1    '
Const fileToRead As String = "C:\Users\david_zemens\Desktop\test.txt"  ' the path of the file to read
Const fileToWrite As String = "C:\Users\david_zemens\Desktop\test_NEW.txt"  ' the path of a new file
Dim FSO As Object
Dim readFile As Object  'the file you will READ
Dim writeFile As Object 'the file you will CREATE
Dim repLine As Variant   'the array of lines you will WRITE
Dim ln As Variant
Dim l As Long

Set FSO = CreateObject("Scripting.FileSystemObject")
Set readFile = FSO.OpenTextFile(fileToRead, ForReading, False)
Set writeFile = FSO.CreateTextFile(fileToWrite, True, False)

'# Read entire file into an array & close it
repLine = Split(readFile.ReadAll, vbNewLine)
readFile.Close

'# iterate the array and do the replacement line by line
For Each ln In repLine
    ln = IIf(InStr(1, ln, "ant", vbTextCompare) > 0, Replace(ln, "t", "wow"), ln)
    repLine(l) = ln
    l = l + 1
Next

'# Write to the array items to the file
writeFile.Write Join(repLine, vbNewLine)
writeFile.Close

'# clean up
Set readFile = Nothing
Set writeFile = Nothing
Set FSO = Nothing

End Sub

Then, depending on whether you want to get rid of the "original" file you could do something like:

'# clean up
Set readFile = Nothing
Set writeFile = Nothing
'# Get rid of the "old" file and replace/rename it with only the new one
Kill fileToRead
Name fileToWrite As fileToRead
End Sub

Solution 2

Here is a much faster and shorter method as compared to the FileSystemObject

Sub Sample()
    Dim MyData As String, strData() As String

    '~~> Read the file in one go!
    Open "C:\Users\Carella Home\Desktop\boomboom.txt" For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)
End Sub

All your text file data is now in the array strData Simply loop though the array and find the text that you want to replace and write it back to the SAME file.

Comments

[aCarella]

Thank you so much. I have seen this used before but it was never explained clearly to me. I will definitely try this out and post my results.

[aCarella]

I have seen this method used before and suggested over referring to files through filesystemobject, but it was never explained. I will look up the syntax and see how much I can pick up. Being that I already append my files through filesystemobject, I figured that it was the better way to use it to edit the files as well. I will look into it. Thank you for the response.

[aCarella]

I just tested this out and it worked perfectly. Thank you so much.

[aCarella]

I am unfamiliar with this. I have nothing to refer to when trying this method out. I am, however, curious as to why this would be more efficient. What is this called? Could you point me in the right direction as to what I would search to learn how to use everything you have typed in your response? Thank you!

[Siddharth Rout]

The reason is very simple. This method is faster because you are not looping though every line of the text file. What you are doing is loading the complete text file in the array (into memory) in one go and then performing the operations on that array.

[David Zemens]

+1 tthis was the other method I could think of, too. I modified my answer to also avoid a .ReadLine and .WriteLine loop.

[aCarella]

Siddharth Routh: What is the name of this method? I want to research it fully so I can start applying it to my code in the future. Thanks for all of your responses so far!

[Siddharth Rout]

There is no name as such for the method. I am just reading the file in binary mode and then storing it in the array using vbcrlf as a delimiter.

[aCarella]

I have this code running at work, and today (I am not sure what) I lost most of the data on my original file. Is there a ways to use fso to do this without deleting and replacing the original file?

[David Zemens]

the original answer I don't think should do that, since you're opening readFile with ForReading. If you did the optional part at the bottom, where Kill fileToRead: Name fileToWrite As fileToRead, that deletes the original file and replaces it with the new file.

[aCarella]

I see. Is there a way to read a file, find what it is that you need to edit/replace in a line, and write/edit that same exact file? I have a few different apps as well as an oracle database using this text file so once every few days I run into some errors when the file is in the processed of being switched.