Populating Content Controls in Word document from Excel workbook (same names)

[Cubajz]

Hi,
I would like to populate Content Controls in embeded Word document with data from Excel named ranges. Content control fields have same names (Title) as named ranges in Excel.

So far I managed with my code to populate only one specified Content Control field from Excel named range (see below) - otherwise code works fine.
Obviously I have many more Content Control´s fields in document and I would like code to go through ALL Content control fields, so that I dont have to do it manualy, one by one.

Thanks in advance for any sugestions.

Sub Populate_ContentControls_in_embeded_document()

Dim WordApp As Object
Dim WordDoc As Object
Dim CC As ContentControl

Set WordApp = GetObject(, "Word.Application")
    
With WordApp
Set WordDoc = .Documents("Dokument v " & ActiveWorkbook.Name) 'example of embeded document name: "Dokument v AAA_ZAKÁZKA.xlsm"
End With


'Go through all CC in embeded Word document and if there is some with title that is the same as excel named range - then populate CC with text from excel named range

For Each CC In WordDoc.ContentControls

    If CC.Title = ActiveWorkbook.Names("hello").Name Then
    CC.Range.Text = Range("hello").Text
    End If

Next


End Sub

[georgiboy]

Try swapping the below:

For Each CC In WordDoc.ContentControls    
    If CC.Title = ActiveWorkbook.Names("hello").Name Then
         CC.Range.Text = Range("hello").Text
    End If
Next

For

On Error Resume Next
 For Each CC In WordDoc.ContentControls
    If CC.Title = ActiveWorkbook.Names(CC.Title).Name Then
         CC.Range.Text = Range(CC.Title).Text
    End If
Next
On Error GoTo 0

Or you could use a UDF to check if the range exists on the active sheet:

Function RangeExists(R As String) As Boolean    
    Dim Test As Range
    On Error Resume Next
    Set Test = ActiveSheet.Range(R)
    RangeExists = Err.Number = 0
End Function

And use it like:

Sub Populate_ContentControls_in_embeded_document()    
    Dim WordApp As Object
    Dim WordDoc As Object
    Dim CC As ContentControl


    Set WordApp = GetObject(, "Word.Application")
    Set WordDoc = WordApp.Documents("Dokument v " & ActiveWorkbook.Name) 'example of embeded document name: "Dokument v AAA_ZAKÁZKA.xlsm"
    
    For Each CC In WordDoc.ContentControls
        If RangeExists(CC.Title) Then
            CC.Range.Text = Range(CC.Title).Text
        End If
    Next


End Sub

All of above is untested.

[macropod]

So far I managed with my code to populate only one specified Content Control field from Excel named range (see below) - otherwise code works fine.
Obviously I have many more Content Control´s fields in document and I would like code to go through ALL Content control fields

Without wanting to place too fine a point on it, Content Controls are not fields - the two are entirely different.

[Cubajz]

Thanks for this clarification Macropod, you are of course right.

I eventualy got and idea for code bellow which works fine, however I am unable to update Content Controls in Header or Footer.

Sub Populate_ContentControls_in_embeded_document()

Dim WordApp As Object
Dim WordDoc As Object
Dim CC As ContentControl
Dim CCName As String

Set WordApp = GetObject(, "Word.Application")
    
With WordApp
Set WordDoc = .Documents("Dokument v " & ActiveWorkbook.Name) 'example of embeded document name: "Dokument v AAA_ZAKÁZKA.xlsm"
End With


'Go through all CC in embeded Word document and if there is some with title that is the same as excel named range - then populate CC with text from excel named range
For Each CC In WordDoc.ContentControls
    CCName = CC.Title
    If CC.Title = ActiveWorkbook.Names(CCName).Name Then
    CC.Range.Text = Range(CCName).Text
    End If

Next

End Sub

I tried this for header but it doesnt work:

For Each CC In WordDoc.Sections(1).headers(wdHeaderFooterPrimary).Range.ContentControls
    CCName = CC.Title
    If CC.Title = ActiveWorkbook.Names(CCName).Name Then
        CC.Range.Text = Range(CCName).Text
    End If
Next

Thanks again for any replies.