|
|
Thread Tools | Display Modes |
#16
|
||||
|
||||
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 |
#17
|
|||
|
|||
Quote:
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:
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 |
#18
|
||||
|
||||
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 |
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 |
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 |
Date auto-populates based on checkbox | mcarter9000 | Word VBA | 5 | 12-23-2010 12:39 PM |