#16
|
|||
|
|||
Thanks for that macropod. As I read your post I realized what I was trying to do was nonsensical. The need I have is to have multiple lines dependent on each dropdown option. The Value field has a character limit, and my text exceeds that limit for every dropdown option. |
#17
|
||||
|
||||
For a workaround that stores the conditional output in document variables, see:
https://www.msofficeforums.com/word-...tml#post119230 The source workbooks are earlier in the thread. Although (in the case of the link) secondary dropdowns are used, that's not to say you'd have to take the same approach.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#18
|
|||
|
|||
Hmm macropod, that looks like a very detailed document. I will take some time to study it and adapt it to my needs. Thanks a ton for your help!
|
#19
|
||||
|
||||
The important thing about what's in the link is that it demonstrates how you can use document variables. The method of getting the data into and out of it/them needn't necessarily be as complex as it was for that thread, where data from multiple workbooks and worksheets were being collated.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#20
|
|||
|
|||
Hey macropod, I successfully found a way around the character limit by storing the excel columns as arrays.
However, in other parts of my document, I am still using value to fill in multiple fields from the dropdown. Which brings me to my question: In your first post in this thread, your code included the following: Code:
With xlWkBk With .Worksheets(StrWkShtNm) ' Find the last-used row in column A. LRow = .Cells(.Rows.Count, 1).End(xlUp).Row ' Populate the content control titled 'ID', with Column A for the 'ID' as the ' content control Text and the values from columns B-E as the content control ' value, using a "|" separator ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Clear For i = 1 To LRow ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _ Text:=Trim(.Range("A" & i)) 'or, for example, to add the contents of column B to the content control's 'value': 'ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _ Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i)) Next End With .Close False End With Thanks |
#21
|
||||
|
||||
Simply use:
Value:=Trim(.Range("B" & i)) & "|" & Trim(.Range("C" & i)) & "|" & Trim(.Range("D" & i)) & "|" & Trim(.Range("E" & i)) Do be aware, though, that the Value property also has limited capacity.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#22
|
|||
|
|||
Ah, that explains it. Thanks!
|
#23
|
|||
|
|||
Paul,
It has been awhile since I have been in this thread and I notice that it gets a lot of visits. I hope you won't feel that I am trying to upstage you in any way, but I thought that I would provide and alternate method that doesn't require the reference to the Excel object library or physically opening the Excel file (with the Excel app). In this method the data is gathered using ADODB in an array then the array is used in various ways to file the CC dropdown list: Code:
Option Explicit Sub Document_Open() Dim strWorkbook As String, strColumns As String Dim lngRowIndex As Long, lngColIndex As Long Dim arrData As Variant Dim oCC As ContentControl Application.ScreenUpdating = False strWorkbook = "D:\Data Stores\Populate Array from Data.xlsx" If Dir(strWorkbook) = "" Then MsgBox "Cannot find the designated workbook: " & strWorkbook, vbExclamation Exit Sub End If Set oCC = ActiveDocument.SelectContentControlsByTitle("ID").Item(1) arrData = fcnExcelDataToArray(strWorkbook, , , False) oCC.DropdownListEntries.Clear 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. 'strColumns = vbNullString 'For lngColIndex = 1 To UBound(arrData, 1) ' strColumns = strColumns & "|" & arrData(lngColIndex, lngRowIndex) 'Next lngColIndex 'strColumns = Right(strColumns, Len(strColumns) - 1) 'oCC.DropdownListEntries.Add arrData(0, lngRowIndex), strColumns 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 |
#24
|
||||
|
||||
Quote:
Your input is always welcome. I've never studied ADODB in any detail, though I am aware of its advantages (higher performance and no need for an Excel reference - though the latter could be avoided though late binding [=yet lower performance]). I probably should study it in more detail; it's yet another tool for users to work with.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#25
|
|||
|
|||
Paul,
I've wrapped up few examples using the ADODB method here: https://gregmaxey.com/word_tip_pages...pdownlist.html |
#26
|
||||
|
||||
Thanks,
I'll take a look. You might find me incorporating ADODB into some of my posts...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#27
|
|||
|
|||
I know this is an older thread but I thought I'd ask for some direction anyway.
I tried the code example and I received a compile error: User-defined type not defined. When I debugged, the error was with this line: Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook |
#28
|
||||
|
||||
Did you read the comment line in the code immediately before that?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#29
|
|||
|
|||
Hi,
i'm using this code for a while and it worked flawless. Now when i open word and run the code i'm getting this error: "method open of object workbooks failed". Does anyone know what can i do? Thank you |
#30
|
||||
|
||||
That suggests you've moved and/or renamed the workbook.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
lesson plan template, teacher help |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Dynamically changing drop-down list based on selection? (Word Form) | laurarem | Word | 1 | 02-21-2013 10:17 PM |
How to import a list of names into PowerPoint from Excel? | CarpetRemnant | PowerPoint | 4 | 12-07-2012 11:08 AM |
Drop down list, Can it be done??? | garethreid | Outlook | 0 | 08-09-2012 06:08 AM |
drop down list from external data source Excel 2007? | Intruder | Excel | 1 | 08-03-2012 05:41 AM |
Long List for drop down box | DLo99 | Word | 0 | 02-28-2010 08:07 AM |