Doing a find and replace in a text file
 

Hi

I have a file from one of our sub systems which I get once
a month.

I need to do a Find and replace on this file for the
following parameters:

Find 002.306003001.4206 Change to 998.306003001.4206

Find 004.306003001.4206 Change to 999.306003001.4206

Find 002.010100001. Change to 998.010100001.

Find 004.010100001. Change to 999.010100001.

Find 002.306003001.4208 Change to 998.306003001.4208

Find 004.307002001.3411 Change to 999.307002001.3411


Also, if we can rename the file once this has completed.

Thanks
Nick

Doing a find and replace in a text file
 

Hi,
You could import the text file into excel, do your find
and replace (record a macro that does this) then save As a
text file.


Otherwise, you could use VB ie not excel. Look at my VB
site:
http://au.geocities.com/windsofmark for some sample
utilities.

I could modify one to do your task. Just send me the text
file

or


Regards
Mark E. Philpot

http://au.geocities.com/windsofmark
http://au.geocities.com/excelmarksway
Please allow 1 to 3 days for a result
(no fee required unless you want to)

-----Original Message-----
Hi

I have a file from one of our sub systems which I get
once
a month.

I need to do a Find and replace on this file for the
following parameters:

Find 002.306003001.4206 Change to 998.306003001.4206

Find 004.306003001.4206 Change to 999.306003001.4206

Find 002.010100001. Change to 998.010100001.

Find 004.010100001. Change to 999.010100001.

Find 002.306003001.4208 Change to 998.306003001.4208

Find 004.307002001.3411 Change to 999.307002001.3411


Also, if we can rename the file once this has completed.

Thanks
Nick
.

Doing a find and replace in a text file
 

There are some scripting procedures that make this a lot easier.

I put the From Values in A1:Axx and the To values in B1:Bxx in a worksheet.

Then I ran this and it seemed to work ok:

Option Explicit
Sub UpDateTxtFile()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myInFileName As String
Dim myOutFileName As String

Dim myArr As Variant
Dim iCtr As Long

myInFileName = "C:\my documents\excel\test.txt"
myOutFileName = "C:\my documents\excel\testout.txt"

With Worksheets("sheet1")
myArr = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.OpenTextFile(myInFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = False
For iCtr = LBound(myArr, 1) To UBound(myArr, 1)
.Pattern = myArr(iCtr, 1)
myContents = .Replace(myContents, myArr(iCtr, 2))
Next iCtr
End With

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

Adjust the names of the input file and output file and the name of the worksheet
that holds the from/to's.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Nick Lancuba wrote:

Hi

I have a file from one of our sub systems which I get once
a month.

I need to do a Find and replace on this file for the
following parameters:

Find 002.306003001.4206 Change to 998.306003001.4206

Find 004.306003001.4206 Change to 999.306003001.4206

Find 002.010100001. Change to 998.010100001.

Find 004.010100001. Change to 999.010100001.

Find 002.306003001.4208 Change to 998.306003001.4208

Find 004.307002001.3411 Change to 999.307002001.3411

Also, if we can rename the file once this has completed.

Thanks
Nick

--

Dave Peterson