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."