Find and Replace using Excel range

dmarie123 · #1 02-15-2013, 02:35 PM

Hi all,

I've spent the day googling and found a bunch of snippets of code but I don't know exactly how to put it all together or if I'm approaching this correctly. The background is this: I have a word document that comes to me with TONS of changes to be made, mostly glaring grammatical errors. I have a lengthy find and replace sequence, so lengthy that I've exceeded “the rules” and it’s no longer performing all the changes.

I've explored writing an additional macro to call up the others but then I found the code from Mr. Paul Edstein that I’ve pasted below and I’m questioning if I’m going about this entirely wrong. So, currently there are about 75+ find and replace actions that need to occur, my word document needs to be searched for words and phrases and then replaced with other words and phrases. Each instance will not necessarily always appear in the word doc. I do have an excel file w/ a find column and the subsequent replacement text. Regardless of how we proceed the following points are key:

1. The macro needs to run until it reaches the end of the excel list so that I can continue to add to the list.
2. Track changes has to be on[/b] for these changes (which I can't figure out how to do in Paul's code below, ARGH!!)

The following macro does work but the string would be very long and updating the macro across more than one computer would be sort of a pain. (found here originally)

Code:

Sub MultiReplace()
Dim StrOld As String, StrNew As String
Dim RngFind As Range, RngTxt As Range, i As Long
StrOld = "A student 9,A student 8,A student 7"
StrNew = "A Student 9,A Student 8,A Student 7"
Set RngTxt = Selection.Range
For i = 0 To UBound(Split(StrOld, ","))
  Set RngFind = RngTxt.Duplicate
  With RngFind.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = Split(StrOld, ",")(i)
    .Replacement.Text = Split(StrNew, ",")(i)
    .Format = False
    .MatchWholeWord = True
    .MatchCase = True
    .MatchAllWordForms = False
    .MatchWildcards = False
    .Execute Replace:=wdReplaceAll
  End With
Next
End Sub

Is it better to run it out of Excel like Paul did below?? Your opinions would be hugely appreciated! I'd like to be able to pass this to one of my colleagues who is also stuck in mundane editing hell

Excel code from Paul's original post:

Code:

Sub ReplaceExcelCellValueInMswordFile()
Dim wdApp As Word.Application, wdDoc As Word.Document
Dim dlg As Variant, dataPath As Variant
Dim iCount As Long, r As Long
Dim strSearch, strReplace As String
r = 3
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
  Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
dlg.Title = "Select your MS word File for replace the word"
dlg.AllowMultiSelect = False
If dlg.Show = -1 Then
  dataPath = dlg.SelectedItems(1)
End If
Set wdDoc = wdApp.Documents.Open(dataPath, AddToRecentFiles:=False)
   wdApp.Visible = True
strSearch = Cells(r, 1).Value
While strSearch <> ""
  strReplace = Cells(r, 2).Value
  iCount = 0
  wdApp.Options.DefaultHighlightColorIndex = wdYellow
  With wdDoc.Content.Find
    .Text = strSearch
    .Replacement.Text = strReplace
    .Replacement.Highlight = True
    .Wrap = wdFindContinue
    .Execute Replace:=wdReplaceAll
  End With
  strSearch = wdDoc.Range.Text
  iCount = (Len(strSearch) - Len(Replace(strSearch, strReplace, ""))) / Len(strReplace)
  If iCount > 1 Then
    wdApp.Options.DefaultHighlightColorIndex = wdRed
     With wdDoc.Content.Find
      .Text = strReplace
      .Replacement.Text = strReplace
      .Replacement.Highlight = True
      .Wrap = wdFindStop
      .Execute Replace:=wdReplaceOne
     End With
  End If
  r = r + 1
  strSearch = Cells(r, 1).Value
Wend
MsgBox "Done"
End Sub

I don’t need the formatting changes but I would add ".MatchCase = True" because some of my changes are simply capitalization.

If I have to choose I think Paul’s code is the best option because I can update a spreadsheet easily. I also like that I get to choose the Word document. However, I don’t know if turning on track changes for the word document is a deal breaker if I’m starting in Excel? As always, any help is appreciated. Also, I get an error about the number of arguments when I run Paul's code on my PC, "450" and MS Word does something where it's opening blank windows that it doesn't allow me to close. Any input is appreciated. Thanks for your time!

Thank you!
Donna

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Paste special an Excel range into Outlook as an Excel Worksheet	charlesh3	Excel Programming	3	02-04-2013 04:33 PM
Bad view when using Find and Find & Replace - Word places found string on top line	paulkaye	Word	4	12-06-2011 11:05 PM
Is there a way to use "find/replace" to find italics words?	slayda	Word	3	09-14-2011 02:16 PM
Help with find and replace or query and replace	shabbaranks	Excel	4	03-19-2011 08:38 AM
Find and Replace within range	anil3b2	Word VBA	3	12-01-2010 02:35 AM