Populate Word content control fields with data from Excel

Kapluke · #1 01-07-2022, 06:29 PM

Hello, I have about 50 fields set as Rich text content controls, some fields are repetitive, in a word document. As some these field values involve calculation, it will be tremendously helpful to map the data for these content controls from an excel file. This will need VBA, I am guessing.

I am a total newbie at this. I have searched some the previous postings but have not made any headway. This one by "gmayor" was helpful but I am not sure how this code gets executed from excel and populates a particular word document. There is no submit or run button from what i can tell.

https://www.msofficeforums.com/word-...ord-excel.html

I am hoping someone can give me a start with one or two fields to map from excel to word CC and explain how this moves from excel to word, that will be really appreciated. Maybe it is as simple as saving the excel file? Sorry, if I sound stupid but that is where I am. Thanks a bunch.

gmayor · #2 01-07-2022, 10:48 PM

The following should help. The Excel macro assumes a two column worksheet with a header row. Column 1 has the titles of the content controls in the named document, column 2 has the values associated with those controls.

The macro runs through each row in column 1 and populates any matching control titles in that column with the associated values from column 2. You can run the macro from a button on the worksheet, or from a button on the ribbon or QAT.

Code:

Sub PopulateCCs()
Const sPath As String = "c:\path\document name.docx"    'the full path of the document
Dim oCC As Object
Dim wdApp As Object
Dim wdDoc As Object
Dim LastRow As Long, lngIndex As Long
Dim xlSheet As Worksheet
Dim sValue As String

    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    ActiveWorkbook.Save
    Set wdDoc = wdApp.Documents.Open(sPath)

    wdApp.Visible = True

    Set xlSheet = ActiveSheet
    With xlSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For lngIndex = 2 To LastRow
            For Each oCC In wdDoc.ContentControls
                If UCase(oCC.Title) = UCase(.Cells(lngIndex, 1)) Then
                    oCC.Range.Text = .Cells(lngIndex, 2)
                    Exit For
                End If
            Next oCC
        Next lngIndex
    End With
lbl_Exit:
    Set wdApp = Nothing
    Set wdDoc = Nothing
    Set oCC = Nothing
    Exit Sub
End Sub

Kapluke · #3 01-08-2022, 03:25 AM

Thank you so much. This was super helpful.

Kapluke · #4 01-08-2022, 07:03 PM

If you don't mind, one other question. Is there a way to protect the word document created above? I changed the statement below so that the original file is retained.

Set wdDoc = wdApp.Documents.Add(sPath)

Can I protect this document when created? Thanks.

gmayor · #5 01-08-2022, 10:05 PM

Protect it for what purpose?

Kapluke · #6 01-09-2022, 04:31 AM

Hi, Sorry, I should have been more specific. I want to protect the entire document content, including the content control data imported from excel, when this new document gets created. I set up protection on the template but that is not retained when the new doc is generated. Thanks.

gmayor · #7 01-09-2022, 10:40 PM

OK, but what do you want to protect it from?

Kapluke · #8 01-10-2022, 02:34 AM

Hi, I would like the protection to prevent the document from being changed (CC and the other content in the doc) once the word file gets created. I am imagining similar to how we can lock an excel sheet. Maybe this is wishful thinking. Thanks.

gmayor · #9 01-10-2022, 05:39 AM

OK. Once the following macro is run, the document is protected as read only - or you could save as PDF.

Code:

Sub PopulateCCs()
Const sPath As String = "c:\path\document name.docx"    'the full path of the document
Dim oCC As Object
Dim wdApp As Object
Dim wdDoc As Object
Dim LastRow As Long, lngIndex As Long
Dim xlSheet As Worksheet
Dim sValue As String
Const sPassword As String = "MyPassword"

    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    ActiveWorkbook.Save
    Set wdDoc = wdApp.Documents.Open(sPath)
    If Not wdDoc.ProtectionType = -1 Then
        wdDoc.Unprotect Password:=sPassword
    End If

    wdApp.Visible = True

    Set xlSheet = ActiveSheet
    With xlSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For lngIndex = 2 To LastRow
            For Each oCC In wdDoc.ContentControls
                oCC.LockContents = False
                If UCase(oCC.Title) = UCase(.Cells(lngIndex, 1)) Then
                    oCC.Range.Text = .Cells(lngIndex, 2)
                    Exit For
                End If
                oCC.LockContents = True
            Next oCC
        Next lngIndex
    End With
    wdDoc.Protect Type:=3, Password:=sPassword
lbl_Exit:
    Set wdApp = Nothing
    Set wdDoc = Nothing
    Set oCC = Nothing
    Exit Sub
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Importing content control data from excel and populate two fields on selected dropdown	badarlodhi	Word VBA	8	02-08-2023 10:47 AM
Push Word content control data and excel cells	shaztastic	Word VBA	13	08-27-2018 06:46 AM
How can I set up tab to go back and forth between legacy fields and content control fields in a Word	Ikajed	Word	1	10-13-2017 06:06 PM
Populate Content Control Dropdowns from Excel	Deirdre Kelly	Word VBA	23	09-07-2017 02:51 PM
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data	Faldinio	Word VBA	7	10-19-2014 06:03 AM