Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing a string within a text file
Hi, I an trying to put together code that will let me replace text in text file. I have copied a previous example off this web site and i works well. The only trouble is I want to find a string within a tex 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 lik 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\Chri 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 an 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 Su -- DHalla ----------------------------------------------------------------------- DHallam's Profile: http://www.excelforum.com/member.php...fo&userid=2525 View this thread: http://www.excelforum.com/showthread.php?threadid=38830 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing a string within a text file
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing a string within a text file
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" a écrit dans le message de news: ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing a string within a text file
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting and replacing the first word in a String of text | Excel Worksheet Functions | |||
Replacing numeric X-axis labels with corresponding text string | Excel Discussion (Misc queries) | |||
Replacing a random string of 5 numbers | Excel Discussion (Misc queries) | |||
Formula for Replacing Text in a String? | Excel Programming | |||
Replacing a value within a string using a formula | Excel Programming |