Use Excel To Find And Replace Words In Txt Files

[HowdeeDoodee]

Can Excel be used to open, find and replace multiple words in text files, and then close and save the text files?

In col A I have the directory and file name where the text files are located.
In col B I have the words or terms to find
In col C I have the words or terms to replace replace when the col B term is found.

I would like the macro to go down the sheet, open the txt files listed in col A.
After the file is open I would like the macro to replace all the terms listed in col B with the associated term on the same row in col C.

The macro must find and replace all of the col B terms with all of the col C terms in all of the txt files, not just the txt file on the same row as the file name.

Thank you in advance for your reply.

Excel Workbook
	A	B	C
1	File Name	Find	Replace With
2	E:\Replace\green.txt	power	powerless
3	E:\Replace\red.txt	trust	distrustful
4	E:\Replace\purple.txt	be free	be confined
5	E:\Replace\brown.txt	not to worry	worry a lot
6	E:\Replace\black.txt	give it away	take it back
7	E:\Replace\Now.txt	NIV HTML Previous Left		8	E:\Replace\Today.txt	NIV HTML Middle1	">Previous Verse (NIV)>>>
9	E:\Replace\Scct.txt	NIV HTML Middle2		10	E:\Replace\Trd.txt	NIV HTML Right	">Next Verse (NIV)
Sheet1

 

[BrianB]

Like this. *** Take care *** the text files are deleted and replaced. Keep backups.

'========================================================================================
'- OPEN TEXT FILES. FIND AND REPLACE TEXT USING A LIST
'========================================================================================
'- Uses a Regular Expression
'- File name column A. Text to find column B. Replacement column C.
'- ** TEXT FILES ARE DELETED AND REPLACED WITH NEW ONES
'- Brian Baulsom August 2010
'========================================================================================
Sub TEXT_REPLACE()
    Dim MyRegExp As Object
    Dim MyPattern As String
    Dim MyMatches As Variant    ' Reg Exp extract set
    Dim FileString As String
    Dim MyFile As String
    Dim NewString As String
    Dim OldText As String  ' lookup value
    Dim NewText As String ' result
    Dim MyRow As Long
    Dim LastRow As Long
    '------------------------------------------------------------------------------------
    Set MyRegExp = CreateObject("VbScript.RegExp")
    Application.Calculation = xlCalculationManual
    '------------------------------------------------------------------------------------
    '- LOOP WORKSHEET ROWS
    LastRow = Range("A65536").End(xlUp).Row
    For MyRow = 2 To LastRow
        Application.StatusBar = MyRow - 1 & " / " & LastRow - 1
        MyFile = Cells(MyRow, "A").Value
        OldText = Cells(MyRow, "B").Value
        NewText = Cells(MyRow, "C").Value
        '-------------------------------------------------------------------------------
        '- READ THE FILE INTO MEMORY AND CLOSE IT
        Open MyFile For Input As #1
            FileString = Input(FileLen(MyFile), #1)
        Close #1
        '-------------------------------------------------------------------------------
        '- EXECUTE REGULAR EXPRESSION REPLACE
        With MyRegExp
            .Global = True
            .pattern = OldText
            .ignorecase = True
            NewString = .Replace(FileString, NewText)
        End With
        '-------------------------------------------------------------------------------
        '- DELETE THE OLD FILE & REPLACE WITH NEW
        Kill MyFile
        Open MyFile For Append As #1
        Print #1, NewString
        Close #1
        '-------------------------------------------------------------------------------
        FileString = ""
        NewString = ""
    Next
    '-----------------------------------------------------------------------------------
    MsgBox ("Done")
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'========== EOP ========================================================================