Amending a text file

[RobinClay]

I have a bunch of routines to create a plain text data file from a spreadsheet.

The files comprise literally thousands - or even tens of thousands - of lines, with usually only a few characters per line.

I want to run through a file - "myFile.txt" - i.e. read in every line, - and if it comes across, say, "ABC", then replace it with, say, "XYZ"

I *could* read in each line, and then write each line into another file, changed as necessary; then delete the old file and rename the new one with the name of the old file.

But is there a quicker / easier way ?

There may not be any occurrence, or there might be two or three - never more - and usually near the end of the file.

[CK76]

Just read the file as one giant string. Then run replace.

It isn't clear what your exact requirement is... but something like below for single file read, replace string(s) and write back.

Sub Demo()
Dim iFile As String: iFile = "C:\Test\full-20190501.txt"
Dim intFF As Integer: intFF = FreeFile()
Dim textData As String

Open iFile For Input As #intFF
textData = Input$(LOF(intFF), #intFF)
Close #intFF

textData = Replace(textData, "Original String", "New String", , , vbBinaryCompare)
Open iFile For Output As #intFF
Print #intFF, textData
Close #intFF
End Sub

EDIT: FYI - Tested on 34Mb log file with 250k+ rows. Took about 3~4 sec. Combine it with DIR() function to iterate over multiple files if needed.

[RobinClay]

CK76 -
Thank you !
That is splendid -
JUST the job.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

By the way, are you really still using Excel 2003? If not, please update your user profile.