Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 09-14-2020, 03:56 PM
Guessed's Avatar
Guessed Guessed is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Yes, the function can be called from any Sub or even another Function. It doesn't have to be an automacro.



Your other macro is getting the data from Excel as an ADODB Recordset and then converting this into an array and then finally into a string by concatenating all the array position contents into a string. So essentially you have the same content in three very different formats.

The point of going all the way to a string is so you can save that in a document metadata store so it can be retrieved any time WITHOUT needing to get it fresh from Excel again. The problem with this format is that you will need to split the contents of that string to firstly break it into rows and then split each row into columns in order to get back to the contents of any single Excel cell.

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.

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #17  
Old 09-15-2020, 04:51 AM
spinn2046@gmail.com spinn2046@gmail.com is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 7 64bit Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2010
Novice
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled
 
Join Date: Aug 2020
Posts: 6
spinn2046@gmail.com is on a distinguished road
Default

Quote:
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.



Quote:
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
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
Reply With Quote
  #18  
Old 09-15-2020, 04:13 PM
Guessed's Avatar
Guessed Guessed is offline Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Windows 10 Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Step 1. Forget the code I posted with XMLMap, customXML and linked CCs. You have invested your time going down a different path. I thought that was the best way to do it but all the stuff you have since learnt is completely different to that pathway. Your chosen pathway is faster to grab the initial data from Excel but slower and more code-intensive everywhere else.

Step 2. It is easier to search a recordset than it is to find something in an array. You can find something in a recordset in three different ways:
a. Use the SQL query with "WHERE [fieldname] = 'target'" which builds the recordset to do the filtering so only records of interest are in the recordset. Use different SQL queries to grab different records if your connection to the database is still open. You have 4 SQL strings but only use one of them to build one recordset. You can build 4 recordsets with 4 queries if you want to.
b. Use the .Filter command to go directly to a record in a recordset
c. Loop through all records until a condition is met (this is the only way to do it with an array)

Step 3. Use the Filter command as listed above

Step 4. Yes

Step 5. recSet!person_number is returning the value stored in the current record's person_number field of recordset resSet.

Step 6. person_number is the name of the field (column title in Excel)

Step 7. Your use of the word Position is confusing me. When dealing with recordsets this is the terminology that aligns with an Excel table-speak
Recordset = Table
Record = Row in Table
Field = Column in Table
Fieldname = each cell in first (or title) row of table
Index = the row number (excluding the title row)

Step 8. Agreed, don't even go to array.

I'm getting lost with the large chunks of info you are posting and where your questions are. How about you step back and digest the information provided above and then come back with a focused question that should be easier to answer. Stay working with the Recordset and don't make the additional steps to arrays or concatenated strings unless you are planning on storing the data for use after the macro finishes.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Tags
word fields, word vba, word vba macro

Thread Tools
Display Modes


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
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled 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
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled Date auto-populates based on checkbox mcarter9000 Word VBA 5 12-23-2010 12:39 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:54 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft