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

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

Just add this line

sFileName = "C:\someotherfilelocation"

right before this line

Open sFileName For Output As iFileNum

The idea is to open and write to a different file than the one you read earlier (C:\filelocation).

If you want to get fancy and show a real "Save As" dialog box, you could do this instead:

sFileName = Application.GetSaveAsFilename()

Answer 2

I have had the same problem and came acrosse this site.

the solution to just set another "filename" in the

... for output as ... command was very simple and useful.

in addition (beyond the Application.GetSaveAsFilename() Dialog)

it is very simple to set a** new filename** just using

the replace command, so you may change the filename/extension

eg. (as from the first post)

sFileName = "C:\filelocation"
iFileNum = FreeFile

Open sFileName For Input As iFileNum
content = (...edit the content) 

Close iFileNum

now just set:

newFilename = replace(sFilename, ".txt", ".csv") to change the extension

or

newFilename = replace(sFilename, ".", "_edit.") for a differrent filename

and then just as before

iFileNum = FreeFile
Open newFileName For Output As iFileNum

Print #iFileNum, content
Close iFileNum 

I surfed over an hour to find out how to rename a txt-file,

with many different solutions, but it could be sooo easy :)

Answer 3

This code will open and read lines of complete text file That variable "ReadedData" Holds the text line in memory

Open "C:\satheesh\myfile\Hello.txt" For Input As #1

do until EOF(1)   

       Input #1, ReadedData
loop**

Answer 4

Guess I'm too late...

Came across the same problem today; here is my solution using FileSystemObject:

Dim objFSO
Const ForReading = 1
Const ForWriting = 2
Dim objTS 'define a TextStream object
Dim strContents As String
Dim fileSpec As String

fileSpec = "C:\Temp\test.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTS = objFSO.OpenTextFile(fileSpec, ForReading)
strContents = objTS.ReadAll
strContents = Replace(strContents, "XXXXX", "YYYY")
objTS.Close

Set objTS = objFSO.OpenTextFile(fileSpec, ForWriting)
objTS.Write strContents
objTS.Close

Answer 5

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:\Temp\test.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