Replacing a string within a text file

[DHallam]

Hi,

I an trying to put together code that will let me replace text in a text file. I have copied a previous example off this web site and it works well. The only trouble is I want to find a string within a text file and replace not only that string but the following 10 strings.

The example below replaces whats in cell A1 with B1 but I would like this to be extended to L2, is this possible?

Many Thanks
Dave

Sub ReplaceTextInFile(SourceFile As String, _
sText As String, rText As String)


Dim TargetFile As String, tLine As String, tString As String
Dim p As Integer, i As Long, F1 As Integer, F2 As Integer
SourceFile = "C:\Documents and Settings\Chris Hallam\Desktop\origionalRESULT.txt"
TargetFile = "C:\Documents and Settings\Chris Hallam\Desktop\New.txt"

If Dir(SourceFile) = "" Then Exit Sub
If Dir(TargetFile) <> "" Then
On Error Resume Next
Kill TargetFile
On Error GoTo 0
If Dir(TargetFile) <> "" Then
'MsgBox TargetFile & _
'" already open, close and delete / rename the file and try again.", _
vbCritical
Exit Sub
End If
End If


F1 = FreeFile
Open SourceFile For Input As F1
F2 = FreeFile
Open TargetFile For Output As F2
i = 1 ' line counter
Application.StatusBar = "Reading data from " & _
TargetFile & " ..."

While Not EOF(F1)
If i Mod 100 = 0 Then Application.StatusBar = _
"Reading line #" & i & " in " & _
TargetFile & " ..."
Line Input #F1, tLine
If sText <> "" Then
ReplaceTextInString tLine, sText, rText
End If
Print #F2, tLine
i = i + 1
Wend

Application.StatusBar = "Closing files ..."

Close F1
Close F2

Kill SourceFile ' delete original file
Name TargetFile As SourceFile ' rename temporary file

Application.StatusBar = False

End Sub

Private Sub ReplaceTextInString(SourceString As String, _
SearchString As String, ReplaceString As String)
Dim p As Integer, NewString As String
Do
p = InStr(p + 1, UCase(SourceString), UCase(SearchString))
If p > 0 Then ' replace SearchString with ReplaceString
NewString = ""
If p > 1 Then NewString = Mid(SourceString, 1, p - 1)
NewString = NewString + ReplaceString
NewString = NewString + Mid(SourceString, _
p + Len(SearchString), Len(SourceString))
p = p + Len(ReplaceString) - 1
SourceString = NewString
End If
If p >= Len(NewString) Then p = 0
Loop Until p = 0
End Sub

Sub TestReplaceTextInFile()
ReplaceTextInFile ThisWorkbook.Path & _
"\ReplaceInTextFile.txt", Range("a2"), Range("b1")
End Sub

[Erasmus Bowen]

Would'nt all that be easier in VBScript ?

"DHallam" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hi,
>
> I an trying to put together code that will let me replace text in a
> text file. I have copied a previous example off this web site and it
> works well. The only trouble is I want to find a string within a text
> file and replace not only that string but the following 10 strings.
>
> The example below replaces whats in cell A1 with B1 but I would like
> this to be extended to L2, is this possible?
>
> Many Thanks
> Dave
>
> Sub ReplaceTextInFile(SourceFile As String, _
> sText As String, rText As String)
>
>
> Dim TargetFile As String, tLine As String, tString As String
> Dim p As Integer, i As Long, F1 As Integer, F2 As Integer
> SourceFile = "C:\Documents and Settings\Chris
> Hallam\Desktop\origionalRESULT.txt"
> TargetFile = "C:\Documents and Settings\Chris Hallam\Desktop\New.txt"
>
> If Dir(SourceFile) = "" Then Exit Sub
> If Dir(TargetFile) <> "" Then
> On Error Resume Next
> Kill TargetFile
> On Error GoTo 0
> If Dir(TargetFile) <> "" Then
> 'MsgBox TargetFile & _
> '" already open, close and delete / rename the file and
> try again.", _
> vbCritical
> Exit Sub
> End If
> End If
>
>
> F1 = FreeFile
> Open SourceFile For Input As F1
> F2 = FreeFile
> Open TargetFile For Output As F2
> i = 1 ' line counter
> Application.StatusBar = "Reading data from " & _
> TargetFile & " ..."
>
> While Not EOF(F1)
> If i Mod 100 = 0 Then Application.StatusBar = _
> "Reading line #" & i & " in " & _
> TargetFile & " ..."
> Line Input #F1, tLine
> If sText <> "" Then
> ReplaceTextInString tLine, sText, rText
> End If
> Print #F2, tLine
> i = i + 1
> Wend
>
> Application.StatusBar = "Closing files ..."
>
> Close F1
> Close F2
>
> Kill SourceFile ' delete original file
> Name TargetFile As SourceFile ' rename temporary file
>
> Application.StatusBar = False
>
> End Sub
>
> Private Sub ReplaceTextInString(SourceString As String, _
> SearchString As String, ReplaceString As String)
> Dim p As Integer, NewString As String
> Do
> p = InStr(p + 1, UCase(SourceString), UCase(SearchString))
> If p > 0 Then ' replace SearchString with ReplaceString
> NewString = ""
> If p > 1 Then NewString = Mid(SourceString, 1, p - 1)
> NewString = NewString + ReplaceString
> NewString = NewString + Mid(SourceString, _
> p + Len(SearchString), Len(SourceString))
> p = p + Len(ReplaceString) - 1
> SourceString = NewString
> End If
> If p >= Len(NewString) Then p = 0
> Loop Until p = 0
> End Sub
>
> Sub TestReplaceTextInFile()
> ReplaceTextInFile ThisWorkbook.Path & _
> "\ReplaceInTextFile.txt", Range("a2"), Range("b1")
> End Sub
>
>
> --
> DHallam
> ------------------------------------------------------------------------
> DHallam's Profile:
> http://www.excelforum.com/member.php...o&userid=25253
> View this thread: http://www.excelforum.com/showthread...hreadid=388303
>

[DHallam]

Hi, What is the format of this code then? What is VBScript? and is there a big difference between the script?

[Erasmus Bowen]

I don't want to send you barking up the wrong tree but here is some basic
info on VBScript.
VBScript is a MS Scripting language that is simple to use (a bit like Basic
in the old days, and quite like VB).
Scripts are text fiules that can run from
a. inside HTML pages (like JavaScript) or from (.hta pages )
b. the DOS command line
c. from Windows explorer, doubleclick on a .vbs file and it will run - if
the wscript engine is enabled

There's lot's of info on what it can do on different sites, Google can find
you all that.
There is a chm help file that is the standard manual and it's full of code
snippets that you van copy and paste into your own script files.
Script files have a .vbs extension.You may already have some on your
machine. Right click and Modify to read before you run !

They can do some pretty powerful things and be quite dangerous when they
interact with the WMI. Windows Management Interface.
You can download and install the engine for free from Microsoft

I find it very useful for manipulating text files and data within text
files.
But
It has practically no interface. Just an input fileld and no communication
with the user except basic message boxes and output files.
You can add an interface by embeding the script in an HTML file with buttons
and bells and renaming the htm extension to hta.
Hta files have access to the File System with the same rights as the session
user.

And then there is a MS site where a bunch of crazies called the MS
Scriptomatic team tell you how to do all sorts of things to remotes
computers too.

If you can handle VBA, you can VBScript. And VBScript can access the Excel
application as an Object and manipulate all of its objects.
Workbooks,.Sheets, Cells, but that's another level.

If you just need data manipulation within text files why not give it a
blast.

Erasmus



http://msdn.microsoft.com/library/de...scriptinga.asp





"DHallam" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hi, What is the format of this code then? What is VBScript? and is there
> a big difference between the script?
>
>
> --
> DHallam
> ------------------------------------------------------------------------
> DHallam's Profile:
> http://www.excelforum.com/member.php...o&userid=25253
> View this thread: http://www.excelforum.com/showthread...hreadid=388303
>

[Tim Williams]

Dave,

If you are happy with VBA then I would continue on that path.
VBScript could do the job but that's true of just about any language
you could think of. The advantage of continuing with Excel/VBA is
Excel gives you somewhere convenient to place your list of term to
replace, somewhere to put buttons to run the code etc etc.

How large are your files? Will the text to replace ever be split
across two lines in the file? If the files are not huge then the
easiest approach would be to read in the whole file and use the
Replace() function to run each of the replace operations in turn

'read in file content to variable strContent

dim rngReplace as range

set rngReplace = activesheet.range("A1")

do while rngReplace.value<>""

strContent = Replace(strContent, rngReplace.value,
rngReplace.offset(0,1).value)

set rngReplace = rngReplace.offset(1,0)
loop

'write out strContent to new file

Tim.


"DHallam" <[email protected]> wrote
in message
news:[email protected]...
>
> Hi,
>
> I an trying to put together code that will let me replace text in a
> text file. I have copied a previous example off this web site and
> it
> works well. The only trouble is I want to find a string within a
> text
> file and replace not only that string but the following 10 strings.
>
> The example below replaces whats in cell A1 with B1 but I would like
> this to be extended to L2, is this possible?
>
> Many Thanks
> Dave
>
> Sub ReplaceTextInFile(SourceFile As String, _
> sText As String, rText As String)
>
>
> Dim TargetFile As String, tLine As String, tString As String
> Dim p As Integer, i As Long, F1 As Integer, F2 As Integer
> SourceFile = "C:\Documents and Settings\Chris
> Hallam\Desktop\origionalRESULT.txt"
> TargetFile = "C:\Documents and Settings\Chris
> Hallam\Desktop\New.txt"
>
> If Dir(SourceFile) = "" Then Exit Sub
> If Dir(TargetFile) <> "" Then
> On Error Resume Next
> Kill TargetFile
> On Error GoTo 0
> If Dir(TargetFile) <> "" Then
> 'MsgBox TargetFile & _
> '" already open, close and delete / rename the file and
> try again.", _
> vbCritical
> Exit Sub
> End If
> End If
>
>
> F1 = FreeFile
> Open SourceFile For Input As F1
> F2 = FreeFile
> Open TargetFile For Output As F2
> i = 1 ' line counter
> Application.StatusBar = "Reading data from " & _
> TargetFile & " ..."
>
> While Not EOF(F1)
> If i Mod 100 = 0 Then Application.StatusBar = _
> "Reading line #" & i & " in " & _
> TargetFile & " ..."
> Line Input #F1, tLine
> If sText <> "" Then
> ReplaceTextInString tLine, sText, rText
> End If
> Print #F2, tLine
> i = i + 1
> Wend
>
> Application.StatusBar = "Closing files ..."
>
> Close F1
> Close F2
>
> Kill SourceFile ' delete original file
> Name TargetFile As SourceFile ' rename temporary file
>
> Application.StatusBar = False
>
> End Sub
>
> Private Sub ReplaceTextInString(SourceString As String, _
> SearchString As String, ReplaceString As String)
> Dim p As Integer, NewString As String
> Do
> p = InStr(p + 1, UCase(SourceString), UCase(SearchString))
> If p > 0 Then ' replace SearchString with ReplaceString
> NewString = ""
> If p > 1 Then NewString = Mid(SourceString, 1, p - 1)
> NewString = NewString + ReplaceString
> NewString = NewString + Mid(SourceString, _
> p + Len(SearchString), Len(SourceString))
> p = p + Len(ReplaceString) - 1
> SourceString = NewString
> End If
> If p >= Len(NewString) Then p = 0
> Loop Until p = 0
> End Sub
>
> Sub TestReplaceTextInFile()
> ReplaceTextInFile ThisWorkbook.Path & _
> "\ReplaceInTextFile.txt", Range("a2"), Range("b1")
> End Sub
>
>
> --
> DHallam
> ------------------------------------------------------------------------
> DHallam's Profile:
> http://www.excelforum.com/member.php...o&userid=25253
> View this thread:
> http://www.excelforum.com/showthread...hreadid=388303
>