Use Excel To Find And Replace Words In Txt Files

HowdeeDoodee

Well-known Member
Joined
Nov 15, 2004
Messages
596
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
ABC
1File NameFindReplace With
2E:\Replace\green.txtpowerpowerless
3E:\Replace\red.txttrustdistrustful
4E:\Replace\purple.txtbe freebe confined
5E:\Replace\brown.txtnot to worryworry a lot
6E:\Replace\black.txtgive it awaytake it back
7E:\Replace\Now.txtNIV HTML Previous Left8E:\Replace\Today.txtNIV HTML Middle1">Previous Verse (NIV)>>>
9E:\Replace\Scct.txtNIV HTML Middle210E:\Replace\Trd.txtNIV HTML Right">Next Verse (NIV)
Sheet1
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Like this. *** Take care *** the text files are deleted and replaced. Keep backups.
Code:
'========================================================================================
'- 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 ========================================================================
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,965
Messages
5,514,454
Members
409,003
Latest member
blaCKwolf

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top