|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Word form that auto-populates (from Excel sheet) other content controls based on one,manually filled
Word form that auto-populates (from Excel sheet) other content controls based on one, manually filled as first (most important) content control.
To say the truth I don't know whether I should use content controls, ActiveX controls or form fields to create my document. There is a word document that I create manually and I'd like to automate it's creation process. There is a dummy excel sheet that contains all data necessary to create this word document and it looks like this: Then there is a document that needs to be created, that consists of customers personal data like person's registration number, person's full name, person's id type and person's id number. The most important of them all, the one the rest will depend upon, is person's registration number (column "B" in the excel sheet). In the image below I've selected the text range where the automation needs to be applied. First content control / form field that's entered manually will be person's registration number (column "B" in the excel sheet). Only digits, 11 of them. And the question is: should I use content control / form field or ActiveX control?? It looks like this: Once person's registration number is entered I expect the two remaining content control / form field or ActiveX controls auto-populate based on the data from the excel sheet. Just take the person's registration number (column "B" in the excel sheet) entered in the word document, search it in the excel sheet and extract person's full name (column "D" in the excel sheet) and auto-fill it into the content control / form field or ActiveX control in the word document. The same needs to be done with person's id number (column "I" in the excel sheet) - I expect it to auto-fill based on data in excel sheet where search is performed using person's registration number (column "B") and person's id number is extracted from column "I" and auto-filled into content control / form field or ActiveX control in the word document. I've checked this post and it gives some directions but still I don't know which to choose: content controls, ActiveX controls or form fields. Second thing I don't know, is how to automatically fill the second and third content control based on data entered manually (person's registration number) in the first content control or ActiveX control. As you can see, unlike the example shown in this video, my document requires 4 different sets of personal data entered respectively in points 2-5. That's why it's so important for me to set these content controls, ActiveX controls or form fields dependent upon the first, manually inserted field - which is in my case - person's registration number (shown in the second image). |
#2
|
|||
|
|||
Content controls for sure (or a userform). What you need to do is create a ADODB connection with EXCEL and load all of the data into an array. Then use that array to populate the master dropdown CC. Use the document content control on exit event to populate the other dependent fields. I'm not going to do this for you, but here is some code that does something similar:
Code:
Option Explicit Private Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean) Dim arrData() As String Dim lngIndex As Long Select Case oCC.Title Case "Name" If Not oCC.ShowingPlaceholderText Then 'Determine which dropdown list entry was selected. Note: The object model has no direct way to do this. For lngIndex = 1 To oCC.DropdownListEntries.Count If oCC.Range.Text = oCC.DropdownListEntries.Item(lngIndex) Then 'Get the data from the CC value property. arrData = Split(oCC.DropdownListEntries.Item(lngIndex).Value, "|") Exit For End If Next lngIndex 'Use that date fill in the transposed name and fill the dependent fields. With oCC .Type = wdContentControlText .Range.Text = arrData(0) .Type = wdContentControlDropdownList End With 'In the Excel data, "~" is used to define linebreaks in the address column. Replace with linebreaks. ActiveDocument.SelectContentControlsByTitle("Address").Item(1).Range.Text = Replace(arrData(1), "~", Chr(11)) ActiveDocument.SelectContentControlsByTitle("Phone Number").Item(1).Range.Text = arrData(2) Else 'Reset the dependent CCs. ActiveDocument.SelectContentControlsByTitle("Address").Item(1).Range.Text = vbNullString ActiveDocument.SelectContentControlsByTitle("Phone Number").Item(1).Range.Text = vbNullString End If Case Else End Select lbl_Exit: Exit Sub End Sub Sub Document_Open() Dim strWorkbook As String, strColumnData As String Dim lngIndex As Long, lngRowIndex As Long, lngColIndex As Long Dim arrData As Variant Dim oCC As ContentControl Application.ScreenUpdating = False strWorkbook = "D:\Data Stores\Fill CC from Excel Data Store.xlsx" If Dir(strWorkbook) = "" Then MsgBox "Cannot find the designated workbook: " & strWorkbook, vbExclamation Exit Sub End If Set oCC = ActiveDocument.SelectContentControlsByTitle("Name").Item(1) arrData = fcnExcelDataToArray(strWorkbook, "Data") If oCC.DropdownListEntries.Item(1).Value = vbNullString Then For lngIndex = oCC.DropdownListEntries.Count To 2 Step -1 oCC.DropdownListEntries.Item(lngIndex).Delete Next lngIndex Else oCC.DropdownListEntries.Clear End If For lngRowIndex = 0 To UBound(arrData, 2) 'Examples: '1. Populate the dropdown list text and value property using data from column 1 'oCC.DropdownListEntries.Add arrData(0, lngRowIndex), arrData(0, lngRowIndex) '2. Populate the dropdown list text property using data from column 1 _ and the value property using data from column 2 'oCC.DropdownListEntries.Add arrData(0, lngRowIndex), arrData(1, lngRowIndex) '3. Populate the dropdown list text property using data from column 1 _ ' and the value property using concanated data from all remaining columns. strColumnData = vbNullString For lngColIndex = 1 To UBound(arrData, 1) strColumnData = strColumnData & "|" & arrData(lngColIndex, lngRowIndex) Next lngColIndex strColumnData = Right(strColumnData, Len(strColumnData) - 1) oCC.DropdownListEntries.Add arrData(0, lngRowIndex), strColumnData Next lbl_Exit: Application.ScreenUpdating = True Exit Sub End Sub Private Function fcnExcelDataToArray(strWorkbook As String, _ Optional strRange As String = "Sheet1", _ Optional bIsSheet As Boolean = True, _ Optional bHeaderRow As Boolean = True) As Variant Dim oRS As Object, oConn As Object Dim lngRows As Long Dim strHeaderYES_NO As String strHeaderYES_NO = "YES" If Not bHeaderRow Then strHeaderYES_NO = "NO" If bIsSheet Then strRange = strRange & "$]" Else strRange = strRange & "]" Set oConn = CreateObject("ADODB.Connection") oConn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=" & strHeaderYES_NO & """;" Set oRS = CreateObject("ADODB.Recordset") oRS.Open "SELECT * FROM [" & strRange, oConn, 2, 1 With oRS .MoveLast lngRows = .RecordCount .MoveFirst End With fcnExcelDataToArray = oRS.GetRows(lngRows) lbl_Exit: If oRS.State = 1 Then oRS.Close Set oRS = Nothing If oConn.State = 1 Then oConn.Close Set oConn = Nothing Exit Function End Function |
#3
|
|||
|
|||
Hey Greg.
Diving into this code you posted right now. I'm not expecting you to do anything for me, but I'd appreciate few hints. This first macro you posted: Code:
Option Explicit Private Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean) Dim arrData() As String Dim lngIndex As Long Select Case oCC.Title Case "Name" If Not oCC.ShowingPlaceholderText Then 'Determine which dropdown list entry was selected. Note: The object model has no direct way to do this. For lngIndex = 1 To oCC.DropdownListEntries.Count If oCC.Range.Text = oCC.DropdownListEntries.Item(lngIndex) Then 'Get the data from the CC value property. arrData = Split(oCC.DropdownListEntries.Item(lngIndex).Value, "|") Exit For End If Next lngIndex 'Use that date fill in the transposed name and fill the dependent fields. With oCC .Type = wdContentControlText .Range.Text = arrData(0) .Type = wdContentControlDropdownList End With 'In the Excel data, "~" is used to define linebreaks in the address column. Replace with linebreaks. ActiveDocument.SelectContentControlsByTitle("Address").Item(1).Range.Text = Replace(arrData(1), "~", Chr(11)) ActiveDocument.SelectContentControlsByTitle("Phone Number").Item(1).Range.Text = arrData(2) Else 'Reset the dependent CCs. ActiveDocument.SelectContentControlsByTitle("Address").Item(1).Range.Text = vbNullString ActiveDocument.SelectContentControlsByTitle("Phone Number").Item(1).Range.Text = vbNullString End If Case Else End Select lbl_Exit: Exit Sub End Sub At the moment I've added main content control (formatted text) (person's registration number) (column "B" in the excel sheet)) to the .docm file and I think the next step is to connect this content control with the column "B" in the excel sheet. It looks like this now - sorry but my Office 2010 is Polish version. What is the next step I should do?? |
#4
|
|||
|
|||
Uhm, I may be missing something.
Is there a reason why you are not using Mail Merge and "Select Recipient?" |
#5
|
||||
|
||||
Greg's Document_ContentControlOnExit macro is showing how to retrieve an array of data from a single list item in a Content Control. It assumes that each entry in a dropdown CC list contains a displayed name plus a 'value' which contains a series of values separated by a "|" symbol. Then when the user makes a selection with that CC, the macro fires to transfer the other data into other content controls (date, address etc).
There are threads on this forum where this type of functionality was requested. https://www.msofficeforums.com/word-...own-lists.html I'm not convinced this macro is what you need for your project but it can be useful for other requirements. The question I have for you is does the number of rows in your data source vary? Your initial sample shows 7 entries and the most recent shows an extra one added. If this project has to adapt to variable numbers of participants it makes the complexity a lot bigger as paragraphs will need to be added/removed accordingly depending on that count. Charles' suggestion of Mail Merge is a possibility but it appears you want all the rows of data in the one interwoven file so I'm going to recommend you stay with the CC + code approach. I'm thinking you might get mileage from repeating content controls and will need to import the excel data as embedded xml to set this up. Because you are using code to transfer the data from Excel to CCs in Word, there are essentially two ways to do this: 1. Write the excel data to an embedded xml file and use linked CCs to display that data 2. Write the excel data direct to CCs (which may or may not be linked to embedded xml) Option 2 is limited to a fixed number of records unless you then include code to expand or reduce the paragraphs for each new record. Option 1 may be more automated to deal with variable record counts if the xml is structured well enough. I will have a play with your provided files to see what I can add.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#6
|
|||
|
|||
Quote:
Currently I'm trying to grasp what are the first two operations I need to perform. I've chosen content control fields to perform this (the whole task I mean) as per GMAXEY recommendation. Quote:
In order to learn how to create ADODB connection to retrieve data from EXCEL file and put them into a content control field inside word document I found this video: How to use ADO and VBA to Read from Worksheets - YouTube Unfortunately, Paul just shows how to extract this data from one Excel sheet into another one. My task is to read data from row B in source data sheet and write it into a Content Control located in word document, which I think is much more difficult task. Nevertheless, from this video I got this code: Code:
Option Explicit Private Sub UseADOSelect() Dim connection As New ADODB.connection Dim exclApp As Excel.Application Dim exclWorkbk As Excel.Workbook Dim mySheet As Excel.Worksheet Dim wordDoc As Word.Document Dim row As Integer Dim i As Integer Dim recSet As New ADODB.Recordset 'All the results of the querry are placed in a record set; Set exclApp = GetObject(, "Excel.Application") Debug.Print exclApp Set wordDoc = Word.ActiveDocument Debug.Print wordDoc Set mySheet = exclApp.ActiveWorkbook.ActiveSheet Debug.Print mySheet.Name Set mySheet = exclApp.ActiveWorkbook.Sheets("sample") Debug.Print mySheet.Name Word.Application.Visible = True connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=X:\Roesler\Excel\FW 1\customer's_dummy_data.xlsm;" & _ "Extended Properties=Excel 12.0 Macro;" & _ "HDR=YES; IMEX=1;" Dim query As String query = "SELECT * From [Simple$]" '[Simple$] is the table name; in this case it's the sheet name; recSet.Open query, connection row = 2 'For i = 1 To 15 'wordDoc.ContentControls(2).Range.Text = exclApp.ActiveWorkbook.Sheets("destinat").Cells(row, 2) 'exclApp.ActiveWorkbook.Sheets("destinat").Cells(row, 2) = wordDoc.ContentControls(i).Range.Text wordDoc.ContentControls(2).Range.Text.Copy wordDoc.ContentControls(2).Range.CopyFromRecordset wordDoc.ContentControls(4).Range.Text = wordDoc.ContentControls(7).Range.Text = wordDoc.ContentControls(9).Range.Text = 'row = row + 1 'Debug.Print row 'Next i connectiom.Close End Sub I've read something like this in this help page. ContentControl.Range property (Word) | Microsoft Docs The code obviously doesn't work. I just showed it to indicate that I'm working on it. It's just the task is overwhelming me but I try my best. I'm counting on some hint over here that pushes me one or two steps further on this task. And thank you so much for all the help up to this point. I've also watched this video to write that code. Push Data from Excel to MS Word Content Controls using Excel VBA - YouTube I don't know how to write the the record set into the content control field inside word document. Code:
'wordDoc.ContentControls(2).Range.Text = exclApp.ActiveWorkbook.Sheets("destinat").Cells(row, 2) 'exclApp.ActiveWorkbook.Sheets("destinat").Cells(row, 2) = wordDoc.ContentControls(i).Range.Text wordDoc.ContentControls(2).Range.Text.Copy wordDoc.ContentControls(2).Range.CopyFromRecordset wordDoc.ContentControls(4).Range.Text = wordDoc.ContentControls(7).Range.Text = wordDoc.ContentControls(9).Range.Text = |
#7
|
|||
|
|||
Andrew,
Thanks, I've been swamped with other non-Word related stuff the last few days and haven't had a chance to address the OP's follow up questions. |
#8
|
|||
|
|||
I have given your the code and process for establishing the ADODB connection and populating an array from an Excel spreadsheet:
Code:
Sub Document_Open() Dim strWorkbook As String, strColumnData As String Dim lngIndex As Long, lngRowIndex As Long, lngColIndex As Long Dim arrData As Variant Dim oCC As ContentControl Application.ScreenUpdating = False strWorkbook = "D:\Data Stores\Fill CC from Excel Data Store.xlsx" If Dir(strWorkbook) = "" Then MsgBox "Cannot find the designated workbook: " & strWorkbook, vbExclamation Exit Sub End If Set oCC = ActiveDocument.SelectContentControlsByTitle("Name").Item(1) arrData = fcnExcelDataToArray(strWorkbook, "Data") If oCC.DropdownListEntries.Item(1).Value = vbNullString Then For lngIndex = oCC.DropdownListEntries.Count To 2 Step -1 oCC.DropdownListEntries.Item(lngIndex).Delete Next lngIndex Else oCC.DropdownListEntries.Clear End If For lngRowIndex = 0 To UBound(arrData, 2) 'Examples: '1. Populate the dropdown list text and value property using data from column 1 'oCC.DropdownListEntries.Add arrData(0, lngRowIndex), arrData(0, lngRowIndex) '2. Populate the dropdown list text property using data from column 1 _ and the value property using data from column 2 'oCC.DropdownListEntries.Add arrData(0, lngRowIndex), arrData(1, lngRowIndex) '3. Populate the dropdown list text property using data from column 1 _ ' and the value property using concanated data from all remaining columns. strColumnData = vbNullString For lngColIndex = 1 To UBound(arrData, 1) strColumnData = strColumnData & "|" & arrData(lngColIndex, lngRowIndex) Next lngColIndex strColumnData = Right(strColumnData, Len(strColumnData) - 1) oCC.DropdownListEntries.Add arrData(0, lngRowIndex), strColumnData Next lbl_Exit: Application.ScreenUpdating = True Exit Sub End Sub Private Function fcnExcelDataToArray(strWorkbook As String, _ Optional strRange As String = "Sheet1", _ Optional bIsSheet As Boolean = True, _ Optional bHeaderRow As Boolean = True) As Variant Dim oRS As Object, oConn As Object Dim lngRows As Long Dim strHeaderYES_NO As String strHeaderYES_NO = "YES" If Not bHeaderRow Then strHeaderYES_NO = "NO" If bIsSheet Then strRange = strRange & "$]" Else strRange = strRange & "]" Set oConn = CreateObject("ADODB.Connection") oConn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=" & strHeaderYES_NO & """;" Set oRS = CreateObject("ADODB.Recordset") oRS.Open "SELECT * FROM [" & strRange, oConn, 2, 1 With oRS .MoveLast lngRows = .RecordCount .MoveFirst End With fcnExcelDataToArray = oRS.GetRows(lngRows) lbl_Exit: If oRS.State = 1 Then oRS.Close Set oRS = Nothing If oConn.State = 1 Then oConn.Close Set oConn = Nothing Exit Function End Function |
#9
|
||||
|
||||
My approach to this is different to Greg's. For my method, I've chosen to add an XMLMap to your Excel workbook and apply it to your tabular datasource. Then use a macro in Word to open the Excel workbook and read that XML data into a string which is then loaded as a CustomXML file stored in the Word document itself.
Once that is done, you can use the Developer Tab's XML Mapping Pane to insert linked Plain Text Content Controls to your Word document anywhere you want this data to appear. The Word document can be saved in that state and if the Excel source is updated, rerun the macro to refresh the CustomXML in the Word document. Any updated CCs in the document will automatically be refreshed as soon as the CustomXML is updated. The macro to use in Word is: Code:
Sub GetNewXML() Dim appXL As Object, oWB As Object, oMap As Object, sPath As String, sXML As String Dim oXPart As CustomXMLPart, sNS As String sNS = "SomeNameSpace" sPath = ActiveDocument.Path & Application.PathSeparator & "customer's_dummy_data.xlsx" Set appXL = CreateObject("Excel.Application") Set oWB = appXL.Workbooks.Open(sPath) Set oMap = oWB.XmlMaps("root_Map") oMap.ExportXML Data:=sXML sXML = Replace(sXML, "<root>", "<root xmlns='" & sNS & "'>") Debug.Print sXML oWB.Close False appXL.Quit Set oMap = Nothing Set oWB = Nothing Set appXL = Nothing 'If CustXML with same Namespace already there, remove it For Each oXPart In ActiveDocument.CustomXMLParts If oXPart.NamespaceURI = sNS Then oXPart.Delete End If Next 'Now add new CustomXML Set oXPart = ActiveDocument.CustomXMLParts.Add(sXML) End Sub If you can get this working for a specific number of records, we can look at ways to automate the insertion or removal of content when the total number of records varies. I can't suggest ways to do that from your document yet because I don't understand the language and can't identify the repeating components.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#10
|
||||
|
||||
I didn't give you feedback on the various other inputs you are taking. I think you are being drowned in information from some very different approaches and end targets. All are very useful as study tools but some may not suit your actual requirements particularly well.
The youtube 'Push Data from Excel to MS Word Content Controls using Excel VBA' video shows a simplistic method of pushing your data to Word. If you had an understanding of that code, you could rewrite it to run from Word to 'pull' the data across. You would however need to be a lot smarter about getting the right data to go into the right CC - and that would need more code. The youtube 'How to use ADO and VBA to Read from Worksheets' gets you access to the Excel recordset and from there you are going to have to work out how to align the matrix of records x fields with locations in your document where those particular values need to go. A recordset allows you to grab the data quickly and also to search through it for a particular record or assigned sort order but it doesn't help you align that content with the locations in your document. In the case of Greg's inputs - he is showing you code to write all the records (a column from Excel) as entries in a single Dropdown Content Control. This will effectively be a single 'name' in the Word document and you can click on the CC and choose a different name. The earlier code showed a method of taking that chosen name and transferring the associated 'row' data into other CCs in the document. This ultimately gives you a single record's worth of data in the Word doc but not all the records (rows) at once.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#11
|
|||
|
|||
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 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 |
#12
|
||||
|
||||
In terms of principles, if your Excel data is contained in a single sheet but you need to grab information from it right through your code then you should only grab it once and store that either in memory or write it to somewhere in the Word doc so that the Excel workbook connection can be closed again. You don't want to keep reconnecting or hold the connection open while other stuff goes on if you can avoid it.
Your code is amalgamating the tabular data from Excel into strings using commas "," as a delimiter which will then enable you to examine the string and work out which column supplied the substring. This will fail spectacularly if strings appear in the source data. For this reason, it is better to use a character as your delimiter that WILL NEVER appear in the source data. For this purpose I tend to use "|" but anything will do. The rows are then separated by carriage returns which should be OK as long as there are none in your Excel source. Then finally your code ends with a disabled line which would have written the gathered data to a userform control. Without this, as soon as the code finishes, the information gathered is lost and you will need to revisit Excel to get it back again. You could alternatively write it to other places but there isn't code there for that.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#13
|
||||
|
||||
Greg's use of functions is part of an effort to create modular code which over the long term enables you to reuse old code and save a lot of time. Many different projects need to get data from Excel. Greg's function enables him to copy and paste that chunk of code into many different projects and saves him lots of rework.
Say you have a large block of code and have 5 instances where you need to go out to Excel to get some tabular data. All 5 instances are essentially the process but the details will vary (what workbook, what sheet, is there headings at the top). Instead of repeating all the necessary coding in 5 places (which then has to be amended in 5 places if there is a problem), a function enables you to write it once and pass in those different input variables to get different datasets from the same code. Subs and functions are similar but functions have the ability to return a variable (in this case a variant array). A function (or a sub with inputs) can't be run directly - they need to be called by other subroutines or functions.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#14
|
|||
|
|||
Quote:
Code:
Private Function fcnExcelDataToArray(strWorkbook As String, _ Optional strRange As String = "Sheet1", _ Optional bIsSheet As Boolean = True, _ Optional bHeaderRow As Boolean = True) As Variant Not by me or other user manually in the Excel cell, right?? It took me a while to grasp that... Going back to my code... What I want to do now with my code, preferably in the end of it, before this line: Code:
' Display the results. ' txtBooks.Text = txt Can anybody help me with that? 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) & ", " 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. txt = Left(txt, Len(txt) - 1) & vbCrLf 'The trailing $ is a type declaration character for the String data type in VBA. Next r '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 |
#15
|
|||
|
|||
While I would likely still use Mail Merge and Choose recipient, I am in awe. Thank you Greg and Andrew.
|
Tags |
word fields, word vba, word vba macro |
|
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 |