Replacing text in a text file

[brianws1o]

I needed to replace some text in several string in a CSV file, so what I did was, I opened the original file as a TextStream using the FileSystemObjec t (set a reference to Microsoft Scripting Runtime). I also created a new empty text file. Now I walk through the original file and write a copy of each line out to my new text file. If I come to a line with the string I'm searching for, I write the corrected new line of text into my new text file.

Keep going to the end of the text file(s). When you're done, save the new text file, delete the original file, and rename the new file to the old one's filename. (I didn

To explain the sample code below, I was searching for a particular string (in this case I was looking for lines like this:

,,R01,222,14876 .56,,,,,,

where I wanted to add a dollar sign to the 5th field (14876.56)

    Dim FSO              As Scripting.FileSystemObject
    Dim tsFix            As Scripting.TextStream
    Dim tsOrig           As Scripting.TextStream
    Dim str_TextLine     As String
    Dim str_TempFile     As String
    Dim as_Items()       As String
    Dim str_OutgoingText As String
    Dim lng_ItemNum      As Long
 
 
Set FSO = New Scripting.FileSystemObject
    If FSO.FileExists(str_SavedCSVFileName) Then
        ' open a new (temp) file
        str_TempFile = "TMP_" & str_SavedCSVFileName
        Set tsFix = FSO.CreateTextFile(str_TempFile, True)
        ' open original CSV file
        Set tsOrig = FSO.OpenTextFile(str_SavedCSVFileName)
            Do Until tsOrig.AtEndOfStream
                str_TextLine = tsOrig.ReadLine
                    If Len(str_TextLine) > 2 And Len(str_TextLine) < 40 Then
                        ' should be a Totals line as below:
                        ' find 4th comma
                        as_Items = Split(str_TextLine, ",")
                            For lng_ItemNum = LBound(as_Items) To UBound(as_Items)
                                ' when we find the 4th comma, if the next char is a number, we'll add a $ in front of it
                                If lng_ItemNum = 4 And IsNumeric(as_Items(lng_ItemNum)) Then
                                    ' this should be a dollar value
                                    If InStr(as_Items(lng_ItemNum), ".") Then
                                        str_OutgoingText = Replace(str_TextLine, as_Items(lng_ItemNum), "$" & as_Items(lng_ItemNum))                                                                            End If
                                End If
                                DoEvents
                            Next
                        ' write out contents of original CSV file but replace line above with one containing $
                        tsFix.WriteLine Replace(str_TextLine, as_Items(lng_ItemNum), "$" & as_Items(lng_ItemNum))
                    Else
                        ' write out contents of original CSV file as is
                        tsFix.WriteLine str_TextLine
                    End If
                DoEvents
            Loop
    End If
 
    tsOrig.Close
    Set tsOrig = Nothing
    tsFix.Close
    Set tsFix = Nothing
    If FSO.FileExists(str_TempFile) Then
        FSO.CopyFile str_TempFile, str_SavedCSVFileName, True
    End If