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