Excel VBA Code to find and replace strings in specified cell range in multiple files

Excel VBA Code to find and replace strings in specified cell range in multiple files

November 27, 2017

You can use the below vba code to find and replace multiple sets of strings in a specified range of cells for all Excel files in a particular directory. 

 

In the below example, the code is set to replace all examples of the entries set by the 'What:=' command in the first row of the first worksheet.  If you want to only have the find and replace operate on columns A to D in the first row, you would edit:

   Worksheets(1).Rows("1").Replace _

. . . to:

Worksheets(1).Range("A1:D1").Replace _

 

Note that each successive range needs to include these three lines listing the worksheet and range, the find term & replace term, and the settings for the letter case and search order.

 

        Worksheets(1).Rows("1").Replace _
        What:="&", Replacement:="AND", _
        SearchOrder:=xlByColumns, MatchCase:=True

 

The  directory containing the files with the data to be replaced is set at the beginning of the macro. 

 

 

 

 

Sub LoopThroughFiles()
   
    FolderName = "R:\Firm\Scannings\osheas\2017.11.21 A4552.0021 Remainder\part 1 v2\process\xls\"
    If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
    fname = Dir(FolderName & "*.xls")
    'loop through the files
    Do While Len(fname)
        With Workbooks.Open(FolderName & fname)
           ' here comes the code for the operations on every file the code finds
        Worksheets(1).Rows("1").Replace _
        What:="&", Replacement:="AND", _
        SearchOrder:=xlByColumns, MatchCase:=True
        Worksheets(1).Rows("1").Replace _
        What:="-", Replacement:="_", _
        SearchOrder:=xlByColumns, MatchCase:=True
        Worksheets(1).Rows("1").Replace _
        What:="%", Replacement:="perect", _
        SearchOrder:=xlByColumns, MatchCase:=True
        Worksheets(1).Rows("1").Replace _
        What:="=", Replacement:="equals", _
        SearchOrder:=xlByColumns, MatchCase:=True
        Worksheets(1).Rows("1").Replace _
        What:="<", Replacement:="_", _
        SearchOrder:=xlByColumns, MatchCase:=True
        Worksheets(1).Rows("1").Replace _
        What:="$", Replacement:="dollar", _
        SearchOrder:=xlByColumns, MatchCase:=True
        ActiveWorkbook.Close SaveChanges:=True
        End With
        ' go to the next file in the folder
        fname = Dir
    Loop
   
End Sub

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com