How to replace a line 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 replace a specific line in that line. I am relatively new to this, since my current code has excel to open a text file and replace what I need to replace, and then save and close it. This method is no longer an option, since having excel opens the text file for too long and holds the file.

This is how far I have come 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 for writing, but I don’t know how to find the line and replace it with the line I need to replace.

Please let me know if, in case you are ready to help / guide me in the right direction, you need additional information. I searched a lot and saw people suggest other ways to do this. I need to learn how to edit lines this way. Can someone please help me?

Thanks in advance!

-Anthony C.

Answer 1

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

I tested this in a small file and seemed to work as expected.

Modified after the answer taken 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 can 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 

Answer 2

Here is a much faster and shorter method compared to 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 strData array strData Just start the loop using the array and find the text you want to replace and return it to the SAME file.