Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-28-2020, 05:27 AM
spinn2046@gmail.com spinn2046@gmail.com is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 7 64bit Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2010
Novice
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled
 
Join Date: Aug 2020
Posts: 6
spinn2046@gmail.com is on a distinguished road
Default Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled

Word form that auto-populates (from Excel sheet) other content controls based on one, manually filled as first (most important) content control.

To say the truth I don't know whether I should use content controls, ActiveX controls or form fields to create my document.

There is a word document that I create manually and I'd like to automate it's creation process.

There is a dummy excel sheet that contains all data necessary to create this word document and it looks like this:





Then there is a document that needs to be created, that consists of customers personal data like person's registration number, person's full name, person's id type and person's id number. The most important of them all, the one the rest will depend upon, is person's registration number (column "B" in the excel sheet).

In the image below I've selected the text range where the automation needs to be applied.



First content control / form field that's entered manually will be person's registration number (column "B" in the excel sheet). Only digits, 11 of them. And the question is: should I use content control / form field or ActiveX control?? It looks like this:



Once person's registration number is entered I expect the two remaining content control / form field or ActiveX controls auto-populate based on the data from the excel sheet.

Just take the person's registration number (column "B" in the excel sheet) entered in the word document, search it in the excel sheet and extract person's full name (column "D" in the excel sheet) and auto-fill it into the content control / form field or ActiveX control in the word document.



The same needs to be done with person's id number (column "I" in the excel sheet) - I expect it to auto-fill based on data in excel sheet where search is performed using person's registration number (column "B") and person's id number is extracted from column "I" and auto-filled into content control / form field or ActiveX control in the word document.



I've checked this post and it gives some directions but still I don't know which to choose: content controls, ActiveX controls or form fields.

Second thing I don't know, is how to automatically fill the second and third content control based on data entered manually (person's registration number) in the first content control or ActiveX control.

As you can see, unlike the example shown in this video, my document requires 4 different sets of personal data entered respectively in points 2-5. That's why it's so important for me to set these content controls, ActiveX controls or form fields dependent upon the first, manually inserted field - which is in my case - person's registration number (shown in the second image).
Reply With Quote
  #2  
Old 08-28-2020, 09:09 AM
gmaxey gmaxey is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Content controls for sure (or a userform). What you need to do is create a ADODB connection with EXCEL and load all of the data into an array. Then use that array to populate the master dropdown CC. Use the document content control on exit event to populate the other dependent fields. I'm not going to do this for you, but here is some code that does something similar:


Code:
Option Explicit

Private Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean)
Dim arrData() As String
Dim lngIndex As Long
  Select Case oCC.Title
    Case "Name"
      If Not oCC.ShowingPlaceholderText Then
        'Determine which dropdown list entry was selected. Note: The object model has no direct way to do this.
        For lngIndex = 1 To oCC.DropdownListEntries.Count
          If oCC.Range.Text = oCC.DropdownListEntries.Item(lngIndex) Then
            'Get the data from the CC value property.
            arrData = Split(oCC.DropdownListEntries.Item(lngIndex).Value, "|")
            Exit For
          End If
        Next lngIndex
        'Use that date fill in the transposed name and fill the dependent fields.
        With oCC
          .Type = wdContentControlText
          .Range.Text = arrData(0)
          .Type = wdContentControlDropdownList
        End With
        'In the Excel data, "~" is used to define linebreaks in the address column.  Replace with linebreaks.
        ActiveDocument.SelectContentControlsByTitle("Address").Item(1).Range.Text = Replace(arrData(1), "~", Chr(11))
        ActiveDocument.SelectContentControlsByTitle("Phone Number").Item(1).Range.Text = arrData(2)
      Else
        'Reset the dependent CCs.
        ActiveDocument.SelectContentControlsByTitle("Address").Item(1).Range.Text = vbNullString
        ActiveDocument.SelectContentControlsByTitle("Phone Number").Item(1).Range.Text = vbNullString
      End If
    Case Else
  End Select
lbl_Exit:
  Exit Sub
End Sub

Sub Document_Open()
Dim strWorkbook As String, strColumnData As String
Dim lngIndex As Long, lngRowIndex As Long, lngColIndex As Long
Dim arrData As Variant
Dim oCC As ContentControl
  Application.ScreenUpdating = False
  strWorkbook = "D:\Data Stores\Fill CC from Excel Data Store.xlsx"
  If Dir(strWorkbook) = "" Then
    MsgBox "Cannot find the designated workbook: " & strWorkbook, vbExclamation
    Exit Sub
  End If
  Set oCC = ActiveDocument.SelectContentControlsByTitle("Name").Item(1)
  arrData = fcnExcelDataToArray(strWorkbook, "Data")
  If oCC.DropdownListEntries.Item(1).Value = vbNullString Then
    For lngIndex = oCC.DropdownListEntries.Count To 2 Step -1
      oCC.DropdownListEntries.Item(lngIndex).Delete
    Next lngIndex
  Else
    oCC.DropdownListEntries.Clear
  End If
  For lngRowIndex = 0 To UBound(arrData, 2)
    'Examples:
    '1. Populate the dropdown list text and value property using data from column 1
    'oCC.DropdownListEntries.Add arrData(0, lngRowIndex), arrData(0, lngRowIndex)
    '2. Populate the dropdown list text property using data from column 1 _
        and the value property using data from column 2
    'oCC.DropdownListEntries.Add arrData(0, lngRowIndex), arrData(1, lngRowIndex)
    '3. Populate the dropdown list text property using data from column 1 _
    '   and the value property using concanated data from all remaining columns.
    strColumnData = vbNullString
    For lngColIndex = 1 To UBound(arrData, 1)
      strColumnData = strColumnData & "|" & arrData(lngColIndex, lngRowIndex)
    Next lngColIndex
    strColumnData = Right(strColumnData, Len(strColumnData) - 1)
    oCC.DropdownListEntries.Add arrData(0, lngRowIndex), strColumnData
  Next
lbl_Exit:
  Application.ScreenUpdating = True
  Exit Sub
End Sub

Private Function fcnExcelDataToArray(strWorkbook As String, _
                                     Optional strRange As String = "Sheet1", _
                                     Optional bIsSheet As Boolean = True, _
                                     Optional bHeaderRow As Boolean = True) As Variant
Dim oRS As Object, oConn As Object
Dim lngRows As Long
Dim strHeaderYES_NO As String
  strHeaderYES_NO = "YES"
  If Not bHeaderRow Then strHeaderYES_NO = "NO"
  If bIsSheet Then strRange = strRange & "$]" Else strRange = strRange & "]"
  Set oConn = CreateObject("ADODB.Connection")
  oConn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & strWorkbook & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=" & strHeaderYES_NO & """;"
  Set oRS = CreateObject("ADODB.Recordset")
  oRS.Open "SELECT * FROM [" & strRange, oConn, 2, 1
  With oRS
    .MoveLast
    lngRows = .RecordCount
    .MoveFirst
  End With
  fcnExcelDataToArray = oRS.GetRows(lngRows)
lbl_Exit:
  If oRS.State = 1 Then oRS.Close
  Set oRS = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
  Exit Function
End Function
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 08-31-2020, 05:19 AM
spinn2046@gmail.com spinn2046@gmail.com is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 7 64bit Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2010
Novice
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled
 
Join Date: Aug 2020
Posts: 6
spinn2046@gmail.com is on a distinguished road
Default

Hey Greg.
Diving into this code you posted right now.
I'm not expecting you to do anything for me, but I'd appreciate few hints.

This first macro you posted:
Code:
Option Explicit

Private Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean)
Dim arrData() As String
Dim lngIndex As Long
  Select Case oCC.Title
    Case "Name"
      If Not oCC.ShowingPlaceholderText Then
        'Determine which dropdown list entry was selected. Note: The object model has no direct way to do this.
        For lngIndex = 1 To oCC.DropdownListEntries.Count
          If oCC.Range.Text = oCC.DropdownListEntries.Item(lngIndex) Then
            'Get the data from the CC value property.
            arrData = Split(oCC.DropdownListEntries.Item(lngIndex).Value, "|")
            Exit For
          End If
        Next lngIndex
        'Use that date fill in the transposed name and fill the dependent fields.
        With oCC
          .Type = wdContentControlText
          .Range.Text = arrData(0)
          .Type = wdContentControlDropdownList
        End With
        'In the Excel data, "~" is used to define linebreaks in the address column.  Replace with linebreaks.
        ActiveDocument.SelectContentControlsByTitle("Address").Item(1).Range.Text = Replace(arrData(1), "~", Chr(11))
        ActiveDocument.SelectContentControlsByTitle("Phone Number").Item(1).Range.Text = arrData(2)
      Else
        'Reset the dependent CCs.
        ActiveDocument.SelectContentControlsByTitle("Address").Item(1).Range.Text = vbNullString
        ActiveDocument.SelectContentControlsByTitle("Phone Number").Item(1).Range.Text = vbNullString
      End If
    Case Else
  End Select
lbl_Exit:
  Exit Sub
End Sub
What does it actually do??

At the moment I've added main content control (formatted text) (person's registration number) (column "B" in the excel sheet)) to the .docm file and I think the next step is to connect this content control with the column "B" in the excel sheet.
It looks like this now - sorry but my Office 2010 is Polish version.








What is the next step I should do??
Reply With Quote
  #4  
Old 08-31-2020, 02:40 PM
Charles Kenyon Charles Kenyon is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,083
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Uhm, I may be missing something.
Is there a reason why you are not using Mail Merge and "Select Recipient?"
Reply With Quote
  #5  
Old 08-31-2020, 05:13 PM
Guessed's Avatar
Guessed Guessed is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Greg's Document_ContentControlOnExit macro is showing how to retrieve an array of data from a single list item in a Content Control. It assumes that each entry in a dropdown CC list contains a displayed name plus a 'value' which contains a series of values separated by a "|" symbol. Then when the user makes a selection with that CC, the macro fires to transfer the other data into other content controls (date, address etc).

There are threads on this forum where this type of functionality was requested. https://www.msofficeforums.com/word-...own-lists.html
I'm not convinced this macro is what you need for your project but it can be useful for other requirements.

The question I have for you is does the number of rows in your data source vary? Your initial sample shows 7 entries and the most recent shows an extra one added. If this project has to adapt to variable numbers of participants it makes the complexity a lot bigger as paragraphs will need to be added/removed accordingly depending on that count.

Charles' suggestion of Mail Merge is a possibility but it appears you want all the rows of data in the one interwoven file so I'm going to recommend you stay with the CC + code approach. I'm thinking you might get mileage from repeating content controls and will need to import the excel data as embedded xml to set this up.

Because you are using code to transfer the data from Excel to CCs in Word, there are essentially two ways to do this:
1. Write the excel data to an embedded xml file and use linked CCs to display that data
2. Write the excel data direct to CCs (which may or may not be linked to embedded xml)

Option 2 is limited to a fixed number of records unless you then include code to expand or reduce the paragraphs for each new record. Option 1 may be more automated to deal with variable record counts if the xml is structured well enough. I will have a play with your provided files to see what I can add.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 09-01-2020, 02:37 PM
gmaxey gmaxey is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Andrew,

Thanks, I've been swamped with other non-Word related stuff the last few days and haven't had a chance to address the OP's follow up questions.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #7  
Old 09-03-2020, 08:28 AM
spinn2046@gmail.com spinn2046@gmail.com is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 7 64bit Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2010
Novice
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled
 
Join Date: Aug 2020
Posts: 6
spinn2046@gmail.com is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
Greg's Document_ContentControlOnExit macro is showing how to retrieve an array of data from a single list item in a Content Control. It assumes that each entry in a dropdown CC list contains a displayed name plus a 'value' which contains a series of values separated by a "|" symbol.
If that's the case, that this macro is rather the end of what I need to do - right?

Currently I'm trying to grasp what are the first two operations I need to perform.
I've chosen content control fields to perform this (the whole task I mean) as per GMAXEY recommendation.


Quote:
Originally Posted by gmaxey
What you need to do is create a ADODB connection with EXCEL and load all of the data into an array.
This is what I'm currently trying to do. Most important thing I want to mention is that the code will be run from Word document.

In order to learn how to create ADODB connection to retrieve data from EXCEL file and put them into a content control field inside word document I found this video:
How to use ADO and VBA to Read from Worksheets - YouTube

Unfortunately, Paul just shows how to extract this data from one Excel sheet into another one.
My task is to read data from row B in source data sheet and write it into a Content Control located in word document, which I think is much more difficult task.

Nevertheless, from this video I got this code:
Code:
Option Explicit

Private Sub UseADOSelect()

Dim connection As New ADODB.connection
Dim exclApp As Excel.Application
Dim exclWorkbk As Excel.Workbook
Dim mySheet As Excel.Worksheet
Dim wordDoc As Word.Document
Dim row As Integer
Dim i As Integer
Dim recSet As New ADODB.Recordset    'All the results of the querry are placed in a record set;

Set exclApp = GetObject(, "Excel.Application")
Debug.Print exclApp

Set wordDoc = Word.ActiveDocument
Debug.Print wordDoc

Set mySheet = exclApp.ActiveWorkbook.ActiveSheet
Debug.Print mySheet.Name
Set mySheet = exclApp.ActiveWorkbook.Sheets("sample")
Debug.Print mySheet.Name

Word.Application.Visible = True

connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=X:\Roesler\Excel\FW 1\customer's_dummy_data.xlsm;" & _
                "Extended Properties=Excel 12.0 Macro;" & _
                "HDR=YES; IMEX=1;"
                                
Dim query As String
query = "SELECT * From [Simple$]"   '[Simple$] is the table name; in this case it's the sheet name;

recSet.Open query, connection

row = 2

'For i = 1 To 15

      'wordDoc.ContentControls(2).Range.Text = exclApp.ActiveWorkbook.Sheets("destinat").Cells(row, 2)
      'exclApp.ActiveWorkbook.Sheets("destinat").Cells(row, 2) = wordDoc.ContentControls(i).Range.Text
      wordDoc.ContentControls(2).Range.Text.Copy
      wordDoc.ContentControls(2).Range.CopyFromRecordset
      wordDoc.ContentControls(4).Range.Text =
      wordDoc.ContentControls(7).Range.Text =
      wordDoc.ContentControls(9).Range.Text =
      
      'row = row + 1
      'Debug.Print row
      
'Next i

connectiom.Close

End Sub
One thing that worries me is, if the Word ContentControl.Range is read only??
I've read something like this in this help page.
ContentControl.Range property (Word) | Microsoft Docs


The code obviously doesn't work. I just showed it to indicate that I'm working on it.
It's just the task is overwhelming me but I try my best.
I'm counting on some hint over here that pushes me one or two steps further on this task.
And thank you so much for all the help up to this point.

I've also watched this video to write that code.
Push Data from Excel to MS Word Content Controls using Excel VBA - YouTube

I don't know how to write the the record set into the content control field inside word document.
Code:
        'wordDoc.ContentControls(2).Range.Text = exclApp.ActiveWorkbook.Sheets("destinat").Cells(row, 2)
        'exclApp.ActiveWorkbook.Sheets("destinat").Cells(row, 2) = wordDoc.ContentControls(i).Range.Text
        wordDoc.ContentControls(2).Range.Text.Copy
        wordDoc.ContentControls(2).Range.CopyFromRecordset
        wordDoc.ContentControls(4).Range.Text =
        wordDoc.ContentControls(7).Range.Text =
        wordDoc.ContentControls(9).Range.Text =
Any ideas?
Reply With Quote
  #8  
Old 09-03-2020, 10:30 AM
gmaxey gmaxey is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

I have given your the code and process for establishing the ADODB connection and populating an array from an Excel spreadsheet:

Code:
Sub Document_Open()
Dim strWorkbook As String, strColumnData As String
Dim lngIndex As Long, lngRowIndex As Long, lngColIndex As Long
Dim arrData As Variant
Dim oCC As ContentControl
  Application.ScreenUpdating = False
  strWorkbook = "D:\Data Stores\Fill CC from Excel Data Store.xlsx"
  If Dir(strWorkbook) = "" Then
    MsgBox "Cannot find the designated workbook: " & strWorkbook, vbExclamation
    Exit Sub
  End If
  Set oCC = ActiveDocument.SelectContentControlsByTitle("Name").Item(1)
  arrData = fcnExcelDataToArray(strWorkbook, "Data")
  If oCC.DropdownListEntries.Item(1).Value = vbNullString Then
    For lngIndex = oCC.DropdownListEntries.Count To 2 Step -1
      oCC.DropdownListEntries.Item(lngIndex).Delete
    Next lngIndex
  Else
    oCC.DropdownListEntries.Clear
  End If
  For lngRowIndex = 0 To UBound(arrData, 2)
    'Examples:
    '1. Populate the dropdown list text and value property using data from column 1
    'oCC.DropdownListEntries.Add arrData(0, lngRowIndex), arrData(0, lngRowIndex)
    '2. Populate the dropdown list text property using data from column 1 _
        and the value property using data from column 2
    'oCC.DropdownListEntries.Add arrData(0, lngRowIndex), arrData(1, lngRowIndex)
    '3. Populate the dropdown list text property using data from column 1 _
    '   and the value property using concanated data from all remaining columns.
    strColumnData = vbNullString
    For lngColIndex = 1 To UBound(arrData, 1)
      strColumnData = strColumnData & "|" & arrData(lngColIndex, lngRowIndex)
    Next lngColIndex
    strColumnData = Right(strColumnData, Len(strColumnData) - 1)
    oCC.DropdownListEntries.Add arrData(0, lngRowIndex), strColumnData
  Next
lbl_Exit:
  Application.ScreenUpdating = True
  Exit Sub
End Sub

Private Function fcnExcelDataToArray(strWorkbook As String, _
                                     Optional strRange As String = "Sheet1", _
                                     Optional bIsSheet As Boolean = True, _
                                     Optional bHeaderRow As Boolean = True) As Variant
Dim oRS As Object, oConn As Object
Dim lngRows As Long
Dim strHeaderYES_NO As String
  strHeaderYES_NO = "YES"
  If Not bHeaderRow Then strHeaderYES_NO = "NO"
  If bIsSheet Then strRange = strRange & "$]" Else strRange = strRange & "]"
  Set oConn = CreateObject("ADODB.Connection")
  oConn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & strWorkbook & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=" & strHeaderYES_NO & """;"
  Set oRS = CreateObject("ADODB.Recordset")
  oRS.Open "SELECT * FROM [" & strRange, oConn, 2, 1
  With oRS
    .MoveLast
    lngRows = .RecordCount
    .MoveFirst
  End With
  fcnExcelDataToArray = oRS.GetRows(lngRows)
lbl_Exit:
  If oRS.State = 1 Then oRS.Close
  Set oRS = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
  Exit Function
End Function
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #9  
Old 09-03-2020, 09:26 PM
Guessed's Avatar
Guessed Guessed is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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.
Attached Files
File Type: xlsx customer's_dummy_data.xlsx (24.9 KB, 20 views)
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #10  
Old 09-03-2020, 10:16 PM
Guessed's Avatar
Guessed Guessed is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

I didn't give you feedback on the various other inputs you are taking. I think you are being drowned in information from some very different approaches and end targets. All are very useful as study tools but some may not suit your actual requirements particularly well.

The youtube 'Push Data from Excel to MS Word Content Controls using Excel VBA' video shows a simplistic method of pushing your data to Word. If you had an understanding of that code, you could rewrite it to run from Word to 'pull' the data across. You would however need to be a lot smarter about getting the right data to go into the right CC - and that would need more code.

The youtube 'How to use ADO and VBA to Read from Worksheets' gets you access to the Excel recordset and from there you are going to have to work out how to align the matrix of records x fields with locations in your document where those particular values need to go. A recordset allows you to grab the data quickly and also to search through it for a particular record or assigned sort order but it doesn't help you align that content with the locations in your document.

In the case of Greg's inputs - he is showing you code to write all the records (a column from Excel) as entries in a single Dropdown Content Control. This will effectively be a single 'name' in the Word document and you can click on the CC and choose a different name. The earlier code showed a method of taking that chosen name and transferring the associated 'row' data into other CCs in the document. This ultimately gives you a single record's worth of data in the Word doc but not all the records (rows) at once.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #11  
Old 09-11-2020, 07:09 AM
spinn2046@gmail.com spinn2046@gmail.com is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 7 64bit Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2010
Novice
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled
 
Join Date: Aug 2020
Posts: 6
spinn2046@gmail.com is on a distinguished road
Default

Hello Guessed and gmaxey.
Thank you both for leading me on a path to start this project.
You are of great help for me, especially Guessed, who explained so much and came out with few possible ideas how to achieve the end goal - I'm so grateful to you Guessed.

Unfortunately gmaxey come up with his solution (content controls + creating a ADODB connection with EXCEL and loading all of the data into a Word VBA array) first and as both these concepts (second by Guessed) involve a lot of new material and new concepts I'll finish this first solution (content controls + creating a ADODB connection with EXCEL and loading all of the data into a Word VBA array) for that alone that I'm in the middle of it all. I've already created a ADODB connection with EXCEL and loaded all of the data into a Word VBA array.

As far as Guessed's solution (add an XMLMap to my Excel workbook and apply it to my tabular datasource.) is concerned I'll head right into it, just after I finish assigning data from the (gmaxey's) Word VBA array into content controls, that I already have in place in my word document.
Since there is a lot of stuff I don't know I'll start writing another question in different thread.
You're all invited to post your answers and ideas

If gmaxey will read this can you tell me, why there is this function (and not a subroutine)
Code:
Private Function fcnExcelDataToArray(strWorkbook As String, _
                                     Optional strRange As String = "Sheet1", _
                                     Optional bIsSheet As Boolean = True, _
                                     Optional bHeaderRow As Boolean = True) As Variant
in the beginning?? It's a beginning, right?? Because there's a ADODB connection with EXCEL established and a record set pulled from the Excel file??
I don't even know how to lunch a function from Word and my team colleagues don't know Excel and they need a solution, that can be executed from Word, preferably automatically like in this "Then when the user makes a selection with that Content Controls, the macro fires to transfer the other data into other content controls (date, address etc)."

The least possible option, for me, is firing this macro with a button or with shortcut assigned to it. But everything from the Word document.

This is my current code:
Code:
Sub GetRows_returns_a_variant_array()


Dim connection As ADODB.connection
Dim recSet As ADODB.Recordset
Dim exclApp As Excel.Application     'This code is written and lunched from Word VBA Editor
Dim exclWorkbk As Excel.Workbook
Dim mySheet As Excel.Worksheet

Dim wordDoc As Word.Document
Dim strPESELfromWord As String
Dim strQuery0 As String
Dim strQuery1 As String
Dim strQuery2 As String
Dim strSexDigit As String
Dim values As Variant           'Should there be values() ??
Dim txt As String
Dim intRemainder As Integer
Dim r As Integer
Dim c As Integer

    Set wordDoc = Word.ActiveDocument
    Debug.Print wordDoc.Name
    Set connection = New ADODB.connection
    Debug.Print "ADODB.connection.Version = " & connection.Version
    strPESELfromWord = Trim(Selection.Text)
    Debug.Print "Wybrano PESEL " & strPESELfromWord & "."

    strSexDigit = Mid(strPESELfromWord, 10, 1)      'Extract 10th digit from PESEL number
    Debug.Print strSexDigit
    intRemainder = strSexDigit Mod 2                'If 1 is remaider it means it's a male, and if there's no remainder, than it's a female.
    Debug.Print intRemainder

    ' Open the database connection.
    connection.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = X:\Roesler\Excel\FW 1\custdb.xlsm;" & _
                "Extended Properties=""Excel 12.0 Macro;HDR=YES;IMEX=1;"";"                 'now it works

    ' Select the data.
    strQuery0 = "SELECT * FROM Books ORDER BY Title, Year"
    strQuery1 = "SELECT * FROM [data$]"      '[data$]   is the table name; in this case it's the sheet name;
    strQuery2 = "SELECT * FROM [data$] WHERE pesel <> ''"      'col B = pesel; col C = data_urzodzenia; col D = imie_nazwisko

    ' Get the records.
    Set recSet = connection.Execute(strQuery2, , adCmdText)

    ' Load the values into a variant array.
    values = recSet.GetRows            'GetRows returns a variant array holding the Recordset's values.

    ' Close the recordset and connection.
    recSet.Close
    connection.Close

    ' Use the array to build a string containing the results.
    For r = LBound(values, 2) To UBound(values, 2)
        For c = LBound(values, 1) To UBound(values, 1)        'max "c" = 78
            txt = txt & values(c, r) & ", "
        Next c
        txt = Left$(txt, Len(txt) - 1) & vbCrLf  'Left Returns a Variant (String) containing a specified number of characters from the left side of a string.
    Next r                                       'The trailing $ is a type declaration character for the String data type in VBA.
                                                 'The result returned from Left$() is a string, whereas Left() returns a Variant.
                     'You must use Left(), not Left$() if there is any chance of Null values, since the Variant can be Null but the String cannot.
    ' Display the results.
    ' txtBooks.Text = txt

End Sub
Reply With Quote
  #12  
Old 09-11-2020, 04:33 PM
Guessed's Avatar
Guessed Guessed is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Greg's use of functions is part of an effort to create modular code which over the long term enables you to reuse old code and save a lot of time. Many different projects need to get data from Excel. Greg's function enables him to copy and paste that chunk of code into many different projects and saves him lots of rework.

Say you have a large block of code and have 5 instances where you need to go out to Excel to get some tabular data. All 5 instances are essentially the process but the details will vary (what workbook, what sheet, is there headings at the top). Instead of repeating all the necessary coding in 5 places (which then has to be amended in 5 places if there is a problem), a function enables you to write it once and pass in those different input variables to get different datasets from the same code.

Subs and functions are similar but functions have the ability to return a variable (in this case a variant array). A function (or a sub with inputs) can't be run directly - they need to be called by other subroutines or functions.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #13  
Old 09-11-2020, 05:10 PM
Guessed's Avatar
Guessed Guessed is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

In terms of principles, if your Excel data is contained in a single sheet but you need to grab information from it right through your code then you should only grab it once and store that either in memory or write it to somewhere in the Word doc so that the Excel workbook connection can be closed again. You don't want to keep reconnecting or hold the connection open while other stuff goes on if you can avoid it.

Your code is amalgamating the tabular data from Excel into strings using commas "," as a delimiter which will then enable you to examine the string and work out which column supplied the substring. This will fail spectacularly if strings appear in the source data. For this reason, it is better to use a character as your delimiter that WILL NEVER appear in the source data. For this purpose I tend to use "|" but anything will do. The rows are then separated by carriage returns which should be OK as long as there are none in your Excel source.

Then finally your code ends with a disabled line which would have written the gathered data to a userform control. Without this, as soon as the code finishes, the information gathered is lost and you will need to revisit Excel to get it back again. You could alternatively write it to other places but there isn't code there for that.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #14  
Old 09-12-2020, 10:35 AM
Charles Kenyon Charles Kenyon is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,083
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

While I would likely still use Mail Merge and Choose recipient, I am in awe. Thank you Greg and Andrew.
Reply With Quote
  #15  
Old 09-14-2020, 06:45 AM
spinn2046@gmail.com spinn2046@gmail.com is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 7 64bit Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2010
Novice
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled
 
Join Date: Aug 2020
Posts: 6
spinn2046@gmail.com is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
Greg's use of functions is part of an effort to create modular code which over the long term enables you to reuse old code and save a lot of time. Many different projects need to get data from Excel. Greg's function enables him to copy and paste that chunk of code into many different projects and saves him lots of rework.
So this Greg's function:
Code:
Private Function fcnExcelDataToArray(strWorkbook As String, _
                                     Optional strRange As String = "Sheet1", _
                                     Optional bIsSheet As Boolean = True, _
                                     Optional bHeaderRow As Boolean = True) As Variant
in this situation, was designed to be called from this subroutine in the middle of the code "Sub Document_Open()", right?
Not by me or other user manually in the Excel cell, right??
It took me a while to grasp that...


Going back to my code...
What I want to do now with my code, preferably in the end of it, before this line:
Code:
    ' Display the results.
    ' txtBooks.Text = txt
is I need to find "strPESELfromWord" value in my array's (It's called "values") 1st row.
Can anybody help me with that?

This is my current code:

Code:
Sub GetRows_returns_a_variant_array()

Dim connection As ADODB.connection
Dim recSet As ADODB.Recordset
Dim exclApp As Excel.Application     'This code is written and lunched from Word VBA Editor
Dim exclWorkbk As Excel.Workbook
Dim mySheet As Excel.Worksheet

Dim wordDoc As Word.Document
Dim strPESELfromWord As String
Dim strQuery0 As String
Dim strQuery1 As String
Dim strQuery2 As String
Dim strSexDigit As String
Dim values As Variant                'Should there be values() ??
Dim txt As String
Dim intRemainder As Integer
Dim r As Integer
Dim c As Integer

    Set wordDoc = Word.ActiveDocument
    Debug.Print wordDoc.Name
    Set connection = New ADODB.connection
    Debug.Print "ADODB.connection.Version = " & connection.Version
    strPESELfromWord = Trim(Selection.Text)
    Debug.Print "Wybrano PESEL " & strPESELfromWord & "."

    strSexDigit = Mid(strPESELfromWord, 10, 1)      'Extract 10th digit from PESEL number
    Debug.Print strSexDigit
    intRemainder = strSexDigit Mod 2                'If 1 is remaider it means it's a male, and if there's no remainder, than it's a female.
    Debug.Print intRemainder

    ' Open the database connection.
    connection.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = X:\Roesler\Excel\FW 1\custdb.xlsm;" & _
                "Extended Properties=""Excel 12.0 Macro;HDR=YES;IMEX=1;"";"                 'now it works

    ' Select the data.
    strQuery0 = "SELECT * FROM Books ORDER BY Title, Year"
    strQuery1 = "SELECT * FROM [data$]"      '[data$]   is the table name; in this case it's the sheet name;
    strQuery2 = "SELECT * FROM [data$] WHERE pesel <> ''"      'col B = pesel; col C = data_urzodzenia; col D = imie_nazwisko

    ' Get the records.
    Set recSet = connection.Execute(strQuery2, , adCmdText)

    ' Load the values into a variant array.
    values = recSet.GetRows            'GetRows returns a variant array holding the Recordset's values.

    ' Close the recordset and connection.
    recSet.Close
    connection.Close

    ' Use the array to build a string containing the results.
    For r = LBound(values, 2) To UBound(values, 2)
        For c = LBound(values, 1) To UBound(values, 1)        'max "c" = 78
           'txt = txt & values(c, r) & ", "
            txt = txt & values(c, r) & "| "
        Next c
        'txt = Left$(txt, Len(txt) - 1) & vbCrLf  'Left Returns a Variant (String) containing a specified number of characters from the left side of a string.
         txt = Left(txt, Len(txt) - 1) & vbCrLf   'The trailing $ is a type declaration character for the String data type in VBA.
    Next r                                       'The result returned from Left$() is a string, whereas Left() returns a Variant.
                              'You must use Left(), not Left$() if there is any chance of Null values, since the Variant can be Null but the String cannot.
                     
    ' Display the results.
    ' txtBooks.Text = txt

End Sub
Reply With Quote
Reply

Tags
word fields, word vba, word vba macro

Thread Tools
Display Modes


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
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled 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
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Date auto-populates based on checkbox mcarter9000 Word VBA 5 12-23-2010 12:39 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:42 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft