Quote:
Originally Posted by Guessed
However, if you want to make use of the data WHILE the macro is still running, you have the choice of using it in either of the earlier formats, where it is still broken up into the individual cells of data.
|
In the current state of my VBA knowledge, how i UNDERSTAND following steps, that need to be taken is:
1. I want to make use of the data WHILE the macro is still running. I'm sorry Andrew, but I don't even know what it means to "add an XMLMap to my Excel workbook and apply it to my tabular datasource." I'll check this all up later, but now I need this macro to populate the content control fields.
2. When recordset is loaded into the array, I want to search for this variable.value in this place I've shown in the previous post pictures. In this array of course.
This "strPESELfromWord" variable contains previously (before macro is lunched) copied PESEL number of a person whose name and ID number (PESEL number is something different then ID number) I need to retrieve from the array.
3. While this variable.value is found in this array, I need to determine it's position and once I have it, I want to retrieve client's name (field name = "imie_nazwisko") which will be in different row in this array but on the same position, than the PESEL number that was found.
4. Once I have this client's name, maybe I need to assign it to some variable and later write it into content control field found by title/tag.
Quote:
Originally Posted by Guessed
Say you wanted to use the Recordset (recSet) to grab all the data from one field. After the recSet has been populated by the first line, you can loop through its records to grab a field
Code:
Set recSet = connection.Execute(strQuery2, , adCmdText)
With recSet
.MoveFirst
Do While Not .EOF
Debug.print recSet!person_number
.MoveNext
Loop
End With
|
5. What is this ! stands for in this line: "Debug.print recSet!person_number"
6. Is "person_number" a variable name / or field name in this line? "Debug.print recSet!person_number"
7. I don't want to grab all the data from one field. I need to find a value (strPESELfromWord.value), that I'm sure is there (because a week earlier from the moment when it's going to be needed, I'll create this record in Excel file), get it's position expressed as Long and get client name from different row in that array, but the same position that PESEL was found.
8. This part of the macro, concatenating all the array position contents into a string, I don't currently use or develop.
I don't want to split the contents of that string later, because I already have this data written into the array, and I want to do all searches in this array, and once I have the PESEL position I will extract client's name from different row and the same position. I just don't know how to write this 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"
'Example, reading an unnamed range of cells: "SELECT * FROM [Sheet1$A1:B10]"
strQuery1 = "SELECT * FROM [data$]" '[data$] is the table name; in this case it's the sheet name;
'Once connected to an Excel workbook, a worksheet or range is the equivalent of a table or view.
'The table name of a worksheet is the name of the worksheet with a dollar sign ("$") appended to it, and surrounded with square brackets ("[" and "]").
strQuery2 = "SELECT * FROM [data$] WHERE pesel <> ''" 'col B = pesel; col C = data_urzodzenia; col D = imie_nazwisko
strQuery3 = "SELECT index, pesel, data_urodzenia FROM [data$]"
'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.
'Use the Recordset (recSet) to grab all the data from one field.
With recSet
.MoveFirst 'https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/movefirst-movelast-movenext-and-moveprevious-methods-ado?view=sql-server-ver15
'Use the MoveFirst method to move the current record position to the first record in the Recordset.
Do While Not .EOF 'Returns a value that indicates whether the current record position is after the last record in a Recordset object.
Debug.Print recSet!person_number
.MoveNext 'Use the MoveNext method to move the current record position one record forward (toward the bottom of the Recordset).
'If the last record is the current record and you call the MoveNext method, ADO sets the current record to the position after the last record in the Recordset (EOF is True).
Loop
End With
' Close the recordset and connection.
recSet.Close
connection.Close
End Sub