Text file in VBA: Open/Find Replace/SaveAs/Close File

Question

Here is pseudocode for what I am hoping to do:

Open text File

Find "XXXXX" and Replace with "YYYY"

Save text File As

Close text file

This is what I have so far

Private Sub CommandButton1_Click()

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String

' Edit as needed
sFileName = "C:filelocation"

iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

sTemp = Replace(sTemp, "DIM A", "1.75")
sTemp = Replace(sTemp, "DIM B", "2.00")
sTemp = Replace(sTemp, "DIM C", "3.00")
sTemp = Replace(sTemp, "DIM D", "4.00")

'Save txt file as (if possible)

iFileNum = FreeFile
Open sFileName For Output As iFileNum

Print #iFileNum, sTemp

Close iFileNum

'Close Userform
Unload UserForm1

End Sub

But instead of overwriting the original text file, I want to "save as" to a new file.

Answer 1

Why involve Notepad?

Sub ReplaceStringInFile()

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String

' Edit as needed
sFileName = "C:Tempest.txt"

iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
    Line Input #iFileNum, sBuf
    sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

sTemp = Replace(sTemp, "THIS", "THAT")

iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum

End Sub