CODEWITHSUNDEEP
excelvbams-wordword-contentcontrol
GettingStarted
GettingStarted

Reputation: 7605

How do I get my Excel data into Word's ContentControl

I have placed a Plain Text Content Control on my Document.

I opened the Macro and have the following code

Sub PrefillDocument()
'
' PrefillDocument Macro
'
'
    Dim docName As ContentControls
    Dim objExcel As Object
    Dim FileName As String
    FileName = ActiveDocument.Path & "\CountyData.xlsx"
    Set objExcel = CreateObject("Excel.Application")
    Set exWb = objExcel.Workbooks.Open(FileName)
    MsgBox exWb.Sheets("4").Cells(1, 2) // Works

    ' Having problems trying to get the data from Excel into the content control
    Set docName = ActiveDocument.SelectContentControlsByTag("Name") // Get 

    docName.Item.Title = exWb.Sheets("4").Cells(1, 2)
    MsgBox docName.Title
    'ActiveDocument.FormFields("Name").Result =
    'ThisDocument.m_name.Caption = exWb.Sheets("Member's Data").Cells(2, 1)

    exWb.Close
    Set exWb = Nothing
End Sub

I have been told NOT to use any legacy controls so I am forced to use the newer ContentControls

Upvotes: 0

Views: 2152

Answers (1)

user1379931
user1379931

Reputation:

docName is a collection of controls, and in this case, Word isn't going to let you apply a Title to every control in the Collection.

So you will need to iterate, e.g.

Dim cc as ContentControl
For Each cc In docName
  cc.Title = exWb.Sheets("4").Cells(1, 2)
Next

or you could probably drop your docName declaration and do

Dim cc as ContentControl
For Each cc In ActiveDocument.SelectContentControlsByTag("Name")
  cc.Title = exWb.Sheets("4").Cells(1, 2)
Next

For the question you posted in the comments, to update the actual content of the Control rather than the title, you need to know that the content is represented by a Word Range, and that you need to set the text of the range, e.g.

cc.Range.Text = exWb.Sheets("4").Cells(1.2)

You will still need to iterate through the collection of controls.

Upvotes: 1

Related Questions