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