View Single Post
Old 09-15-2020, 04:51 AM is offline Windows 7 64bit Office 2010
Join Date: Aug 2020
Posts: 6 is on a distinguished road

Originally Posted by Guessed View Post
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.

Originally Posted by Guessed View Post
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
Set recSet = connection.Execute(strQuery2, , adCmdText)
With recSet
  Do While Not .EOF
    Debug.print recSet!person_number
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.

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      '
                         '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).
    End With
    ' Close the recordset and connection.

End Sub
Reply With Quote