Consulting

Results 1 to 4 of 4

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

  1. #1
    VBAX Regular
    Joined
    May 2015
    Posts
    30
    Location

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

    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

  2. #2
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    747
    Location
    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.
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  3. #3
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,351
    Location
    Quote Originally Posted by Cubajz View Post
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    VBAX Regular
    Joined
    May 2015
    Posts
    30
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •