asked on

Use Word VBA to set Content Control from Excel Spreadsheet

I'm working on a Word document that has a dropdown list and a plain text content control, I want the plain text to be filled based on the dropdown selection. I have the dropdown entries and plain text description in an Excel document. I have been able to successfully do this by assigning the description to the value of the dropdown entry, but the character limit is too small. Is there any way to implement this?
Thanks!

Neil Fleming

Can you post the code that populates the dropdown?

If your VBA is already opening the Excel source file, it should be fairly easy to read the text description on the fly based on the choice in the dropdown.

Vikas Kini

ASKER

Thanks for your reply. My code looks like this:

Sub Document_Open()
Application.ScreenUpdating = False
Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook
Dim StrWkBkNm As String, StrWkShtNm As String, LRow As Long, i As Long
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Dropdown Information.xlsx"
StrWkShtNmTestNo = "Test Number"

With xlApp
  .Visible = False
  
  Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMRU:=False)
  
  With xlWkBk
      With .Worksheets(StrWkShtNmTestNo)
        LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        ActiveDocument.SelectContentControlsByTitle("TestNo")(1).DropdownListEntries.Clear
        For i = 2 To LRow
          ActiveDocument.SelectContentControlsByTitle("TestNo")(1).DropdownListEntries.Add _
            Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i))
        Next
      End With

    .Close False
  End With
  .Quit
End With
Set xlWkBk = Nothing: Set xlApp = Nothing
Application.ScreenUpdating = True
End Sub

Open in new window

Here, the dropdown options are in column A, and upon selecting them I want the descriptions from column B to be entered into a plain text content control.

ASKER CERTIFIED SOLUTION

Neil Fleming

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tracy

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Neil Fleming (https:#a42365790)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9
Experts-Exchange Cleanup Volunteer