How can I edit a line in a file opened with FileSystemObject in VBA?

Question

I'm looping through an input file and using the readline command to read each line, check it for various criteria, then I want to make changes based on the result. Here is a very simple version of what I'm trying to do:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileLoc, 1)

Do While Not objFile.AtEndOfStream
     strLineRead = objFile.readline
     if strLineRead Like "*text to change*" Then
          'Some code to change the line
     end if
Loop

What I've been doing is saving the entire file to a string called strFileText, and then using a Replace function to replace the strLineRead within that string with the changed version. Something like this:

strFileText = Replace(strFileText, strLineRead, strNewLine)

and then write that entire string to a new text file.

The problem is, sometimes I might have a line where it's entire text is "NC", and then doing a find/replace on the entire file for "NC" changes more than just the one line.

So is there a command in the FileSystemObject, while on a certain line, to be able to alter the file directly? I'm thinking something like a "writeline" command.

Answer 1

Have these private subs somewhere in your file and on an event, call them. First call replace_text and fill it with the requirements. See my sample code.

    Private Sub Command3_Click()

    Dim sFileName As String
    Dim fileSys As Variant

    ' Edit as needed
    sFileName = Me.FileList.Value

    Set fileSys = CreateObject("Scripting.FileSystemObject")

    Replace_Text sFileName, "bad text", "good text", fileSys
    End Sub
    Private Sub Replace_Text(targetFile As String, targetText As String, replaceText As String, fileSys As Variant)
    If Right(targetFile, 3) = "filepath extension you want (example: xml or doc etc.)" Then
        Update_File targetFile, targetText, replaceText, fileSys
    Else
        MsgBox "You did not select the right file. Please try again."
    End If
    End Sub
    Private Sub Update_File(fileToUpdate As String, targetText As String, replaceText As String, fileSys As Variant)

    Dim tempName As String
    Dim tempFile As Variant
    Dim file As Variant
    Dim currentLine As String
    Dim newLine As String


        'creates a temp file and outputs the original files contents but with the replacements
        tempName = fileToUpdate & ".tmp"
        Set tempFile = fileSys.CreateTextFile(tempName, True)

        'open the original file and for each line replace any matching text
        Set file = fileSys.OpenTextFile(fileToUpdate)
        Do Until file.AtEndOfStream
            currentLine = file.ReadLine
            newLine = Replace(currentLine, targetText, replaceText)
            'write to the new line containing replacements to the temp file
            tempFile.WriteLine newLine
        Loop
        file.Close

        tempFile.Close

        'delete the original file and replace with the temporary file
        fileSys.DeleteFile fileToUpdate, True
        fileSys.MoveFile tempName, fileToUpdate
    End Sub