Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled

Guessed · #1 09-03-2020, 09:26 PM

My approach to this is different to Greg's. For my method, I've chosen to add an XMLMap to your Excel workbook and apply it to your tabular datasource. Then use a macro in Word to open the Excel workbook and read that XML data into a string which is then loaded as a CustomXML file stored in the Word document itself.

Once that is done, you can use the Developer Tab's XML Mapping Pane to insert linked Plain Text Content Controls to your Word document anywhere you want this data to appear. The Word document can be saved in that state and if the Excel source is updated, rerun the macro to refresh the CustomXML in the Word document. Any updated CCs in the document will automatically be refreshed as soon as the CustomXML is updated.

The macro to use in Word is:

Code:

Sub GetNewXML()
  Dim appXL As Object, oWB As Object, oMap As Object, sPath As String, sXML As String
  Dim oXPart As CustomXMLPart, sNS As String
  
  sNS = "SomeNameSpace"
  sPath = ActiveDocument.Path & Application.PathSeparator & "customer's_dummy_data.xlsx"
  Set appXL = CreateObject("Excel.Application")
  Set oWB = appXL.Workbooks.Open(sPath)
  Set oMap = oWB.XmlMaps("root_Map")
  oMap.ExportXML Data:=sXML
  sXML = Replace(sXML, "<root>", "<root xmlns='" & sNS & "'>")
  Debug.Print sXML
  oWB.Close False
  appXL.Quit
  Set oMap = Nothing
  Set oWB = Nothing
  Set appXL = Nothing
  
  'If CustXML with same Namespace already there, remove it
  For Each oXPart In ActiveDocument.CustomXMLParts
    If oXPart.NamespaceURI = sNS Then
      oXPart.Delete
    End If
  Next
  
  'Now add new CustomXML
  Set oXPart = ActiveDocument.CustomXMLParts.Add(sXML)
  
End Sub

The source Excel file which contains your data and XML Map is attached.

If you can get this working for a specific number of records, we can look at ways to automate the insertion or removal of content when the total number of records varies. I can't suggest ways to do that from your document yet because I don't understand the language and can't identify the repeating components.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Reset "Button" to clear Word user-filled form, from all filled details.	Rafi	Word VBA	20	01-20-2023 02:16 PM
VBA to Populate text content controls in word from excel	lmoir87	Word VBA	15	05-27-2022 04:22 PM
Auto populate form (data from excel) in Word based on drop down list selection (data from excel)	wvlls	Word VBA	1	03-22-2019 02:29 PM
Form with content controls - expands but at the bottom of the form	louiseword	Word	3	05-27-2016 12:47 AM
Date auto-populates based on checkbox	mcarter9000	Word VBA	5	12-23-2010 12:39 PM