Using textstream object to replace tab with spaces and delete characters

Question

I have over a thousand .s2p files (a delimited text file used by electrical testing equipment) that were edited by a VBA macro, which opened each raw file in Excel as a tab-and-space delimited text file, and replaced a few of the columns with data columns from another file, then saved them in original format (.s2p) and closed them. This is the call I used to open the each file:

    Call Application.Workbooks.OpenText(Filename:=(path & filename & ".s2p"), Origin:="437", DataType:=xlDelimited, ConsecutiveDelimiter:=True, Tab:=True, Space:=True, TextQualifier:=xlTextQualifierNone)

Now, when I open the .s2p files in Notepad to view them, the original single space separating the columns is now a full tab, and some double quote (") characters were added to some of the file header lines (despite setting TextQualifier parameter to xlTextQualifierNone...)

Because of this I want to write another macro that can open all of these .s2p files again, loop through the lines, and replace any double quotes with blanks, and any tabs with single spaces. I was planning to use .OpenAsTextStream on each file, but it seems like the TextStream object doesn't support overwriting lines, but can only write new lines...

Is there a better way to achieve what I am trying to do than just reading lines from the original file, and writing them to a newly created file? It is imperative that I save the final file as ".s2p" and not ".txt".

Answer 1

You don't need the text streams, just the basic input output actions available in VBA.

Option Explicit

Sub test()
    FixFile "c:\temp\mytestfile.s2p"
End Sub

Sub FixFile(filename As String)
    Dim fnum As Integer
    Dim fileText As String
    Dim finalText As String

    fnum = FreeFile
    On Error Resume Next
    Open filename For Input As #fnum
    If Err.Number <> 0 Then
        Debug.Print "Critical error attempting to open " & filename & _
                    ". Error #" & Err.Number & ": " & Err.Description
        Exit Sub
    End If

    finalText = ""
    Do Until EOF(fnum)
        Line Input #fnum, fileText
        fileText = Replace(fileText, """", " ", , , vbTextCompare)
        fileText = Replace(fileText, vbTab, " ", , , vbTextCompare)
        finalText = finalText & fileText & vbCrLf
    Loop
    Close fnum

    fnum = FreeFile
    Open filename For Output As #fnum
    Print #fnum, finalText
    Close fnum

End Sub

Edited to show line by line read with a final write.