Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find part text, replace all text | Excel Discussion (Misc queries) | |||
Find & replace with text | Excel Discussion (Misc queries) | |||
Replace can't find text | Excel Discussion (Misc queries) | |||
find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
Find & Replace in Text Box | Excel Discussion (Misc queries) |