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

The Problem (Q-score 4, ranked #91st of 95 in the VBA Core archive)

The scenario as originally posted in 2013

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:UsersCarella HomeDesktopboomboom.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:UsersCarella HomeDesktopboomboom.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.

Why the Win32 API declaration is fragile here

This problem involves a Declare statement, which means 32-bit vs 64-bit compatibility is in play. Office 64-bit requires the PtrSafe keyword and LongPtr data types for any handles — the most common root cause of the exact symptom described.

The Verified Solution — niche answer (below median) (+7)

38-line VBA Core pattern (copy-ready)

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:Usersdavid_zemensDesktoptest.txt"  ' the path of the file to read
Const fileToWrite As String = "C:Usersdavid_zemensDesktoptest_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

Loop-performance notes specific to this pattern

The loop in the answer iterates in process. On a 2026 Office build, setting Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual around a loop of this size typically cuts runtime by 40–70%. Re-enable both in the Exit handler.

When to Use It — classic (2013–2016)

Ranked #91st in its category — specialized fit

This pattern sits in the 94% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the VBA Core archive for a higher-consensus alternative.

What changed between 2013 and 2026

The answer is 13 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.