VBA Using Excel 2016 to Populate Word 2016 Content Controls

Question

I'm attempting to populate a Word.dotm document that has content control text boxes using Excel.

In Excel, I create the word objects and open the file, but I can't figure out how to actually access the controls.

In the Word doc's VBA, I can access them via

.SelectContentControlsByTitle("control").Item(1).Range.Text = "..."

but that doesn't work using an Object in Excel..

Here's the code I've tried in Excel:

Dim objWord         As Object
Dim objDoc          As Object
Dim objSelection    As Object
Dim ctrl            As Word.ContentControl

Set objWord = CreateObject("Word.Application")

Set objDoc = objWord.Documents.Add(ThisWorkbook.Path & "\MyDoc.dotm")

objWord.Visible = True

Set objSelection = objWord.Selection

'Doesn't work
objDoc.SelectContentControlsByTitle("control").Item(1).Range.Text = "..."        

'Doesn't work either, but tried nonetheless
For Each ctrl In objDoc.contentcontrols
    If ctrl.Title = "control" Then
        ctrl.Range.Text = "!"
        Exit For
    End If
 Next ctrl

UPDATE:

I didn't have the MS Word 16.0 Object Library referenced... the early binding and the tip to copy the template file rather than altering it directly mentioned by Timothy Rylatt now works and John Korchok's For Each loop works as well. Their additions have been added to the code above. Thank you!

Please edit the question with the code you've tried in Excel and describe exactly how "that doesn't work". The same basic approach should work, but "the devil is in the details" - you need to show us the details... — Cindy Meister, Dec 31 '19 at 22:36
Don't forget to describe what goes wrong - does Word display an error, or does nothing apparently happen? (Here, the objDoc.SelectContentControlsByTitle("control").Item(1).Range.Text = "..." line works fine. — , Jan 01 '20 at 01:29

score 1 · Answer 1 · answered Dec 31 '19 at 22:34

In any macro, but especially in cross-program macros, avoid using the Selection object and use Range object instead. Sometimes using the range object requires a slightly less direct route to accessing the control, such as having to poll all content controls to find which one has the correct title:

Sub SetCCText()
    Dim CC As ContentControl
    For Each CC In ActiveDocument.ContentControls
        If CC.Title = "control" Then
            CC.Range.Text = "Text"
        End If
    Next CC
End Sub

score 1 · Accepted Answer · answered Jan 01 '20 at 12:30

1

Although you have declared ctrl as a ContentControl it is not a Word content control, which is why the For Each loop won't work.

If you want to use early binding you need to specify the library that the object type belongs to in the variable declaration. Some libraries have objects with the same name but which are completely different.

Dim ctrl As Word.ContentControl

NB: You should also create a document from the Word template rather than edit the template directly:

Set objDoc = objWord.Documents.Add(ThisWorkbook.Path & "\MyDoc.dotm")

answered Jan 01 '20 at 12:30

Timothy Rylatt

7,221
2
10
14

I tried the early binding method you presented but it still doesn't seem to work. I get "Compile Error: User-Defined type not recognized" when I attempt to run the function. – RLee Jan 02 '20 at 15:35
When using early binding you need to set a reference to the library you wish to use. From the tools menu select References then find the library you wish to use in the list. Or, be consistent and just use late binding as you have with your other object variables. – Timothy Rylatt Jan 02 '20 at 20:18
For more information see https://stackoverflow.com/questions/50293956/convert-early-binding-code-to-late-binding – Timothy Rylatt Jan 02 '20 at 20:26

VBA Using Excel 2016 to Populate Word 2016 Content Controls

2 Answers2