Find and Replace Multiple Text Strings on Multiple Text Files from a folder

Question

I'm working on the vba code to accomplish the following tasks

Word Document Open a Text file from the folder
Find and replace the text (multiple Text) based on a excel sheet (which have find what and replace with)
Process all text files in the folder and save it.

I would like to customize the below code for the above requirement,

I'm using Office 2016 and I think I have to replace Application.FileSearch in the script to ApplicationFileSearch for 2003 and prior office editions.

I try to accomplish using the word macro recorder and also used notepad++, I've recorded in Notepad++ also and it works for one file, I would like to do batch process all files in the folder and save it after replacing the text.

As there is too many lines there to replace more than 30 or more lines to replace, I would like the code to look for the text from a excel file like find what and replace with columns.

 Sub FindReplaceAllDocsInFolder( )
 Dim i As Integer
 Dim doc As Document
 Dim rng As Range

 With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents"
.SearchSubFolders = False
.FileType = msoFileTypeWordDocuments
If Not .Execute( ) = 0 Then
    For i = 1 To .FoundFiles.Count
         Set doc = Documents.Open(.FoundFiles(i))
            For Each rng In doc.StoryRanges
             With rng.Find
                .ClearFormatting
                .Replacement.ClearFormatting
                .Text = "Dewey & Cheatem"
                .Replacement.Text = "Dewey, Cheatem & Howe"
                .Forward = True
                .Wrap = wdFindContinue
                .Format = False
                .MatchCase = False
                .MatchWholeWord = False
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
                .Execute Replace:=wdReplaceAll
             End With
            Next rng
         doc.Save
         doc.Close

         Set rng = Nothing
         Set doc = Nothing
    Next i
Else
    MsgBox "No files matched " & .FileName
End If
 End With
 End Sub

Thanks Jay

Doug Coats · Accepted Answer · 2018-03-28 13:39:40Z

Borrowed from https://social.msdn.microsoft.com/Forums/en-US/62fceda5-b21a-40b6-857c-ad28f12c1b23/use-excel-vba-to-open-a-text-file-and-search-it-for-a-specific-string?forum=isvvba

Sub SearchTextFile()
    Const strFileName = "C:\MyFiles\TextFile.txt"
    Const strSearch = "Some Text"
    Dim strLine As String
    Dim f As Integer
    Dim lngLine As Long
    Dim blnFound As Boolean
    f = FreeFile
    Open strFileName For Input As #f
    Do While Not EOF(f)
        lngLine = lngLine + 1
        Line Input #f, strLine
        If InStr(1, strLine, strSearch, vbBinaryCompare) > 0 Then
            MsgBox "Search string found in line " & lngLine, vbInformation
            blnFound = True
            Exit Do
        End If
    Loop
    Close #f
    If Not blnFound Then
        MsgBox "Search string not found", vbInformation
    End If
End Sub

This is simply just finding the match. You can use the built in function "Replace" which land the total fix. You would also have to fit in the "loop through files" code, which here is a snippet.

Dim StrFile As String
StrFile = Dir(pathtofile & "\*" & ".txt")
Do While Len(StrFile) > 0
    Debug.Print StrFile
    StrFile = Dir
Loop

I wouldve made this a comment, but it was too much text. This isnt meant to be a full blown answer, just giving you the pieces you need to put it all together on your own.

Jayakumar krishnamoorthy · Accepted Answer · 2018-03-29 06:31:45Z

1

Thanks for all your help. I have found alternate solution using the below EXE. FART.exe (FART - Find and Replace Text). I have create a batch file with the below command example.

https://emtunc.org/blog/03/2011/farting-the-easy-way-find-and-replace-text/

http://fart-it.sourceforge.net/

Examples:

fart "C:\APRIL2011\Scripts\someFile.txt" oldText newText

This line instructs FART to simply replace the string oldText with newText.

fart -i -r "C:\APRIL2011\Scripts*.prm" march2011 APRIL2011

This line will instruct FART to recursively (-r) search for any file with the .prm extension in the \Scripts folder. The -i flag tells FART to ignore case-sensitivity when looking for the string.

answered Mar 29, 2018 at 6:31

Jayakumar krishnamoorthy

231 silver badge9 bronze badges

Collectives™ on Stack Overflow

Find and Replace Multiple Text Strings on Multiple Text Files from a folder

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related