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

spinn2046@gmail.com · #1 09-03-2020, 08:28 AM

Quote:

Originally Posted by Guessed

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?

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