VBA to Populate text content controls in word from excel

WeberJazz · #16 05-27-2022, 04:22 PM

Thanks SO much for the help with this question.

I have used a stand alone app called Word Control Toolkit for years to populate controls from data gathered in Excel. The app worked great. Once I had the spreadsheet set up, data was filled in and an xml map was produced that was literally copied and pasted into the app with everything falling into place. Each client has a single data file with multiple forms using some of the data items.

With a new employer and rather than installing the Toolkit that is no longer supported, I decided to code VBA to do basically the same thing. The code in this thread was the starting point and it works almost perfectly. The code here opens the Word doc, finds data in the spreadsheet with matching titles and replaces the text. Thanks gmayor for the elegant solution.

Code:

Sub PopulateCCs(QPathName)

    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(QPathName)

        wdApp.Visible = True
    
        Set xlSheet = ActiveSheet
        With xlSheet
            LastRow = .Cells(.Rows.Count, "V").End(xlUp).Row
            For lngIndex = 3 To LastRow
                For Each oCC In wdDoc.ContentControls
                    If oCC.Title = .Cells(lngIndex, 22) Then
                        oCC.Range.Text = .Cells(lngIndex, 25)
                        Exit For
                    End If
                Next oCC
            Next lngIndex
        End With
        
        WhatAreTheCCs ("To check that everything fits")
lbl_Exit:
    Set wdApp = Nothing
    Set wdDoc = Nothing
    Set oCC = Nothing
Exit Sub
End Sub

The sub reference near the bottom simply creates a list of all controls and content on a separate sheet in the Excel file. The pathname is extracted from the Excel file and passed to this function and is working fine. The list of data points is in columns V-Y (22-25) of the Excel file and also appears to be linking appropriately.

The problem is for every form I create, four or five controls just don't get changed. I'm running off of older templates and there was some inconsistency with the control titles (a space in the middle or a changed naming convention) that left some of the controls not getting changed. No problem. I fix those in the template by updating the title in the control properties and run again. But the fixed ones don't want to update.

Tried a possible solution by just creating a brand new control at the same location with the right title. Now these new controls are also not updating. It still has the default text "Click or tap here to enter text."

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Content Controls for large quantities of text	tonia8675309	Word	5	06-08-2018 04:42 PM
Populate Content Control Dropdowns from Excel	Deirdre Kelly	Word VBA	23	09-07-2017 02:51 PM
Rich text/Plain text Content Controls in Template	michael.fisher5	Word	9	11-19-2014 06:36 AM
Moving between Rich text content controls	Sammie0Sue	Word	4	03-12-2014 01:43 AM
Rich Text Content Controls: Formatting?	tinfanide	Word VBA	8	03-04-2013 04:15 AM