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?