Microsoft Office Forums - View Single Post - [Solved] VBA to Populate text content controls in word from excel

LauraBlair · 11-22-2021, 08:22 PM

Hi all, I am doing something similar to the original poster, but I cannot get your original code to work with the loop, any assistance would be greatly appreciated.

I have named the cells in excel, and i have given all content controls in the work doc a title, but still wont work. Could you please assist me in getting this code to work?

Thank you so much

Code:

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 name being referenced for CC in Word
                oCC.Range.Text = CAseName 'This is the 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