Using Excel VBA, how to search for a specific word and insert comments against that word in a Word document?

Question

I am trying to create an excel based tool that reviews Word documents for specific errors. I want this tool to search for a word/sentence and insert a comment against it. I have written a code (please see below) that is able to highlight the word/sentence, however, unable to insert the comment.

Here is my code so far:

Sub Ref_Figs_Tbls()

    Dim wdDoc As Object

    Set wdDoc = ActiveDocument

    With wdDoc
        With .Range
            With .Find
                .ClearFormatting
                .Replacement.ClearFormatting
                .MatchWildcards = True
                .Wrap = wdFindStop
                .Text = "Reference source not found"
                .Replacement.Text = ""
                .Execute
            End With

            Do While .Find.Found = True

                .Select
                .HighlightColorIndex = wdRed

                .Select
                Selection.Comments.Add Range:=Selection.Range
                Selection.TypeText Text:="Cross referencing error"

                .Collapse wdCollapseEnd
                .Find.Execute
            Loop
        End With
    End With

End Sub

why using Excel? The most straightforward way is a Word based tool. — DisplayName, Sep 04 '18 at 06:24
Um... it looks like you are working with _fields_. A field is different to normal document text as it displays a result of something... You may need to select the entire field (not just the contents) in order to add a comment... — SlowLearner, Sep 04 '18 at 06:58

DisplayName · Accepted Answer · 2018-09-04T07:59:04.797

Since you say you're acting from within Excel Application, then an unqualified Selection object would reference the host application, i.e. it'd return the Excel Selection edited to add a Word host application code

Hence you have to explicitly qualify Word application object as the Parent of the wanted Selection object (which I can't see any trace of in your code, though...)

Sub Ref_Figs_Tbls()


    Dim WordApp As Object

    'try and get Word application object, or exit sub
    Set WordApp = GetObject(, "Word.Application")
    If WordApp Is Nothing Then Set WordApp = CreateObject("Word.Application")
    If WordApp Is Nothing Then: MsgBox "Can't get a Word instance", vbCritical: Exit Sub

    With WordApp.ActiveDocument ' reference word application currently active document
        With .Range
            With .Find
                .ClearFormatting
                .Replacement.ClearFormatting
                .MatchWildcards = True
                .Wrap = wdFindStop
                .text = "Reference source not found"
                .Replacement.text = ""
                .Execute
             End With

            Do While .Find.Found = True
                .Select
                With WordApp.Selection ' explicitly reference Word application object selection
                    .Range.HighlightColorIndex = wdRed
                    .Range.Comments.Add Range:=.Range '.Find.Parent
                    .text = "Cross referencing error"
                End With
                .Collapse wdCollapseEnd
                .Find.Execute
            Loop
        End With
    End With
    Set WordApp = Nothing
End Sub

BTW you don't need all that Select/Selection work, and you can directly work with wanted objects

for instance the Do While .Find.Found = True loop can turn into

        Do While .Find.Found = True
            With .Find ' reference the Find object
                .Parent.HighlightColorIndex = wdRed ' set Find Parent object (i.e. its Range) color
                .Parent.Comments.Add(Range:=.Parent).Range.text = "Cross referencing error" ' set Find Parent object (i.e. its Range) comment object text
                .Execute
            End With
        Loop

using Word as host application, the above code would simplify to:

Option Explicit

Sub Ref_Figs_Tbls()

    Dim wdDoc As Document

    Set wdDoc = ActiveDocument

    With wdDoc
        With .Range
            With .Find
                .ClearFormatting
                .Replacement.ClearFormatting
                .MatchWildcards = True
                .Wrap = wdFindStop
                .Text = "Reference source not found"
                .Replacement.Text = ""
                .Execute
             End With

            Do While .Find.Found = True
                With .Find
                    .Parent.HighlightColorIndex = wdRed
                    .Parent.Comments.Add(Range:=.Parent).Range.Text = "Cross referencing error"
                    .Execute
                End With
            Loop
        End With
    End With

End Sub

This worked. Thank you so much. I am a Mechanical Engineer with very limited VBA knowledge. That's the reason my code is crude. As part of my job, I need to maintain a lot of reports/documents and it has become difficult for me to maintain quality of all the documents and therefore, decided to use VBA to cut down my review time. Thank you once again so much for your help — Veda Prakash Chinta, Sep 04 '18 at 07:42
you're welcome. and I'd confirm my suggestion: if you need to automate word documents quality, then code in VBA word directly instead of working form Excel host application — DisplayName, Sep 04 '18 at 07:45
Ok. I started to realize this now. I will code in VBA Word instead. Thank you :-) — Veda Prakash Chinta, Sep 04 '18 at 07:53
Fine. I added the same code for word VBA: as you can see it's the same but without all the Word Application object setting and referencing — DisplayName, Sep 04 '18 at 07:59
Fantastic. This is so simple and works great. I will continue coding in word. Thank you. — Veda Prakash Chinta, Sep 04 '18 at 09:15

Using Excel VBA, how to search for a specific word and insert comments against that word in a Word document?

1 Answers1

Linked