Find/replace with track changes not working

Rafaelete

New Member
Joined
Feb 2, 2022
Messages
14
Office Version
  1. 2007
Platform
  1. Windows
Hello,

I´m trying to find and replace multiple sentences from different word files from an specific folder. The words that I´m trying to change are on two columns in excel:
1649347219893.png

The code is working fine, but due to the specifications of the work that I´m doing, I have to activate trackchanges on each word. The problem with this, is that for some reason, the changes that the find and replace has already done are maintained in the word and makes the sentence appear as if is still there. Even though is changed.
In column B, the last two sentences are the same, but one is longer: AFC - Administration Iberia - Accounting Iberia (Energetic Business). Based on the order that the changes happen, each sentence is change from top to bottom. So this should be replace with "REVISAR" and then the last sentence: AFC- Administration Iberia Accounting Iberia should not be replaced. But here is where I run into a problem:
1649347503732.png

When I passed the code step by step, it first changed the sentence AFC - Administration Iberia Accounting Iberia (Energetic Business) with REVISAR. But for some reason it still detected the line "AFC - Administration Iberia - Accounting Iberia" and replaced it with DEBUG.

When I run the code with .trackchanges=False the result appears fine. Taking into account the first time the sentence has been changed with "REVISAR", which is what I need with trackchanges=True:
1649347952901.png



Hopefully I have explained the problem clearly enough.


This is the code that I have developed to make the find and replace on each word (Note:sFileName is just the rute that I use to select all the words).

Dim Wbk As Workbook: Set Wbk = ThisWorkbook
Dim Wrd As New Word.Application
Dim Dict As Object
Dim RefList As Range, RefElem As Range
Dim Key
Dim wrdRng As Range
Dim WDoc As Document


Wrd.Visible = True

Set WDoc = Wrd.Documents.Open(filename:=sFileName, OpenAndRepair:=True) 'Modify as necessary.

Debug.Print sFileName

' Activar control de cambios en cada documento
With WDoc:
.TrackRevisions = True
WDoc.ActiveWindow.View.MarkupMode = wdBalloonRevisions
'WDoc.ActiveWindow.View = wdConflictView
End With

'Assigns the columns that is going to have the original texts that need to be changed
Set Dict = CreateObject("Scripting.Dictionary")
Set RefList = Wbk.Sheets("Reemplazos").Range("B2:B50") 'Modify as necessary.


'Selects the column that´s one column to the right of the reference column
With Dict
For Each RefElem In RefList
If Not .Exists(RefElem) And Not IsEmpty(RefElem) Then
.Add RefElem.Value, RefElem.Offset(0, 1).Value
Debug.Print RefElem
End If
Next RefElem
End With

'Assigns the conditions and loops through each text to replace it
For Each Key In Dict
With WDoc.Content.FIND
Application.ScreenUpdating = False
Debug.Print Key
.ClearFormatting
.Replacement.ClearFormatting
.Text = Key
.Replacement.Text = Dict(Key)
.MatchAllWordForms = False
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWildcards = False
.MatchSoundsLike = False
.Execute Replace:=2
End With

Next Key


'Saves, Closes and quits the words.

WDoc.SaveAs NewNewWordName(sFileName)
WDoc.Close
Wrd.Quit




I have been running my head around this problem for a couple of days and can´t seem to find a solution. Any help will be greatly appreciated.

Thanks a lot.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top