VBA to Populate text content controls in word from excel

[LauraBlair]

Hi all, I am fairly new to VBA and i have found a code which i have altered slightly to populate text content controls in word.

I have named the cells in excel, and i have given all content controls in the work doc a title, but still cant get it to work. The code runs, but cant seem to access the content controls so the word doc opens but the content controls remain blank.

Any assistance would be greatly appreciated, what am i missing?

Thank you

Sub Exec_Summary2()
'
Dim objWord As Object
UserName = Environ$("username")
Dim oCC As ContentControl


Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Activate

objWord.Documents.Open ("https://XXXX-my.sharepoint.com/personal/" & UserName & "_XXXX/Documents/_MyProfile/Desktop/Business%20Executive%20Summary%20MASTER.docx?web=1")

    For Each oCC In ActiveDocument.ContentControls
        Select Case oCC.Title
            Case "CASENAME" 'This is the Title being referenced for CC in Word
                oCC.Range.Text = CAseName 'This is the named cell being referenced in excel
            Case "title of control 2"
                oCC.Range.Text = whatever
            Case "title of control 3"
                oCC.Range.Text = whatever
                'etc
        End Select
    Next oCC

End Sub

 

[Dan_W]

Hi, so I gather that you are running this VBA code In Excel? That being the case, Excel wouldn't automatically understand what you mean when you say ContentControl because such a thing doesn't exist (in Excel...) You will likely encounter the same problem with ActiveDocument. Also, rather confusingly, while the Range object exists in both Word and Excel, they are very different.

You can help Excel/VBA by adding a reference to the Word Object Library to your project. This would allow Excel to understand all the specific Word object and particular VBAisms. When you are in the VBA IDE (the code environment, go to Tools -> References -> MS Word Object Library:

Select it and then click ok. Hopefully your code will start working.

IF you do use this approach (called early-binding), you might want to consider the slightly revised code below:

Sub Exec_Summary2()

    Dim objWord As Word.Application
    Dim oCC As ContentControl
    Dim Username As String
    Dim ActDoc As Word.Document
    
    Set objWord = New Word.Application
    objWord.Visible = True
    Username = Environ$("username")
    
    Set ActDoc = objWord.Documents.Open("https://XXXX-my.sharepoint.com/personal/" & Username & "_XXXX/Documents/_MyProfile/Desktop/Business%20Executive%20Summary%20MASTER.docx?web=1")

    For Each oCC In ActDoc.ContentControls
        Select Case oCC.Title
            Case "CASENAME"                 ' This is the Title being referenced for CC in Word
                oCC.Range.Text = CaseName   ' This is the named cell being referenced in excel
            Case "title of control 2"
                oCC.Range.Text = whatever
            Case "title of control 3"
                oCC.Range.Text = whatever
                'etc
        End Select
    Next oCC

    ' You may want to tidy up when you've finished by close down documents and closing word, etc.
    ' ActDoc.Close
    ' objWord.Quit
    ' Set ActDoc = Nothing
    ' Set objWord = Nothing
    
End Sub

Please let me know if you have any questions, or if you need any help making this work.

 

[Dan_W]

DId you manage to get it working?