Anyone used ADO to modify text files? (1 Viewer)

[peskywinnets]

I'm a little out my comfort zone here & could use some guidance, I basically want to read in a text file, iterate down through the whole file line by line & replace any occurrences of £ with nothing. Here's what I've got...

Dim objStream, strData
Set objStream = CreateObject("ADODB.Stream")
objStream.Charset = "utf-8"
objStream.LineSeparator = 10
objStream.Open
objStream.LoadFromFile ("C:\Users\PHT\Downloads\report.txt")
objStream.Position = 0
'strData = objStream.ReadText()

    I = 1
    While Not objStream.EOS
        strContent = objStream.ReadText(-2)
            strContent = Replace(strContent, "£", "")
            objStream.WriteText strContent
    Wend

objStream.SaveToFile ("C:\Users\PHT\Downloads\report1.txt")

It works, except in the output file (report1.txt) I have two lines - the original line & a modified copy of it (where the £ has been removed successfully)....I just want the modified line to appear in the output file....but can't quite wrap my head around how to achieve this.

 

[peskywinnets]

Ok, I sussed it, I needed to have another ADO stream (which I called objStreamOut) to write only the modifed text line to...

Dim objStream, strData
Set objStream = CreateObject("ADODB.Stream")
Set objStreamOut = CreateObject("ADODB.Stream")
objStream.Charset = "utf-8"
objStreamOut.Charset = "utf-8"
objStream.LineSeparator = 10
objStream.Open
objStreamOut.Open
objStream.LoadFromFile ("C:\Users\PHT\Downloads\report.txt")
objStreamOut.Open
objStream.Position = 0
objStreamOut.Position = 0

'strData = objStream.ReadText()

    I = 1
    While Not objStream.EOS
        strContent = objStream.ReadText(-2)
        Debug.Print strContent
        If I > 3 Then
            strContent = Replace(strContent, ",", ".") & vbCrLf
            strContent = Replace(strContent, "£", "") & vbCrLf
            objStreamOut.WriteText strContent
        Debug.Print strContent
        End If
        I = I + 1
    Wend

objStreamOut.SaveToFile ("C:\Users\PHT\Downloads\report1.txt")

objStream.Close
objStreamOut.Close
Set objStream = Nothing
Set objStreamOut = Nothing

...it all works fine