Avatar of Vikas Kini
Vikas Kini
 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?
VBAMicrosoft ExcelMicrosoft WordMicrosoft Office

Avatar of undefined
Last Comment

8/22/2022 - Mon
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

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
        For i = 2 To LRow
          ActiveDocument.SelectContentControlsByTitle("TestNo")(1).DropdownListEntries.Add _
            Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i))
      End With

    .Close False
  End With
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.
Neil Fleming

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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.

Experts-Exchange Cleanup Volunteer
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck