Find and replace, excel macro that opens word document

Question

I am trying to make a macro to open a word document and make track changes in accordance with column A and B.

I got this to work, but only if the document that is opened in the track changes mode "Simple Markup".

If it is in any other mode, and I have the following search sentences.

A1: al anden personer B1: alle andre mennesker
A2: anden personer    B2: andre mennesker

And the text in the word document is "al anden personer".

The text will be "alle andre menneskerandre mennesker" in other world it will search in the track changes.

Therefore, I am trying to make the Word document always open in simple markup. I have tried using iteration of

ActiveWindow.View.RevisionsFilter.Markup = wdRevisionsMarkupSimple

but could not get it to work.

Hope you can help.

PS: I am fairly new to VBA so if you have any other improvement or hint the I'm all ears.

My code right now is:

Option Explicit

Const wdReplaceAll = 2

Sub FindReplace()
    Dim wordApp As Object
    Dim wordDoc As Object
    Dim myStoryRange As Object
    Dim cell As Range
    Dim Find1 As String
    Dim Replace1 As String
    Const wdRevisionsMarkupSimple As Integer = 1
    'Dim oRevision As Revision

If Not FileIsOpen("H:\Til excel replace test ark" & ".docx") Then
    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = True
    Set wordDoc = wordApp.Documents.Open("H:\Til excel replace test ark.docx")
    wordDoc.trackrevisions = True
    'ActiveWindow.View.RevisionsFilter.Markup = wdRevisionsMarkupSimple cannot get it to work
Else
    On Error GoTo ExitSub
End If
    


With Worksheets("sheet1")
For Each cell In Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    Find1 = cell.Value
    Replace1 = cell.Offset(0, 1).Value
    
    
    For Each myStoryRange In wordDoc.StoryRanges
        With myStoryRange.Find
            .MatchCase = True
            .matchwholeword = True
            .Text = Find1
            .Forward = True
            .MatchAllWordForms = False
            .MatchSoundsLike = False
            .MatchWildcards = False
            .Replacement.Text = Replace1
            .Execute Replace:=wdReplaceAll
        End With
    Next myStoryRange
    
Next cell
End With

Exit Sub
ExitSub:
MsgBox "Luk word document før du benytter denne macro"


End Sub
Public Function FileIsOpen(FullFilePath As String) As Boolean

    Dim ff As Long

    On Error Resume Next

    ff = FreeFile()
    Open FullFilePath For Input Lock Read As #ff
    Close ff
    FileIsOpen = (Err.Number <> 0)

    On Error GoTo 0

End Function

Timothy Rylatt Timothy Rylatt · Accepted Answer · 2020-09-07T21:13:13

Your issue is a result of your use of late binding.

When using late binding you cannot use the enums or constants from the Word object library, e.g. wdRevisionsMarkupSimple, as Excel doesn't know what those represent. You either have to declare those constants yourself or use their underlying values.

So to activate revisions with simple markup your code needs to be:

ActiveWindow.View.RevisionsFilter.Markup = 1 'wdRevisionsMarkupSimple

EDIT: I also missed something else obvious - Excel also has ActiveWindow in its object model. When writing code across applications you need to be absolutely scrupulous in specifying which application/object the line of code refers to. In this case it should be:

WordApp.ActiveWindow.View.RevisionsFilter.Markup = 1 'wdRevisionsMarkupSimple

You can avoid these errors by adding Option Explicit at the top of the code module. This will prevent your code from compiling when you have undeclared variables. To add this automatically open the VBE and go to Tools | Options. In the Options dialog ensure that Require Variable Declaration is checked.

Find and replace, excel macro that opens word document

1 Answers