Importing Display Name & Values into a Content Control Dropdown List

[richardray]

Hi,

I have a content control dropdown list in Word 2010 and I have hundreds of entries to add. The Display Name and Value data differs so to enter them one by one is going to be a painful task.

Is there a way for me to populate this through VBA? I thought I might be able to use something like:

ActiveDocument.FormFields("ddlServiceCat_1").DropDown.ListEntries
.Add "jkhjkhjkhjk"

So I could then just stick my list into Excel and wrap the VBA around it and then copy into the VBA script but I am no expert on this so was hoping that someone might know how to do this.

I must stress that I am not looking to have a dynamic feed from an Excel spreadsheet - the form will be sent externally to suppliers to gather information which is then imported into our Business Process software and scraped into an online form.

I have looked through the forum to check for posts relating to this but I couldn't find any that give me exactly what I need.

Thanks in advance.

Richard

[gmayor]

You can do this from a list in Excel. Let's assume that you have the list in Column 1 of the worksheet C:\Path\Dropdown Entries List.xlsx. Then the following will work for the named content control ddlServiceCat_1. The macro leaves the workbook open.

'Hundreds' of items may be a stretch for a content control. Have you consider a userform with a list or combo box? That could read the workbook directly into the list/combo box. You can find instructions on how to create and fill the userform on my web site linked from the sig.

Sub FillCC()
Dim oCC As ContentControl
Dim bCCtrl As Boolean
Dim xlApp As Object
Dim xlBook As Object
Dim LastRow As Long, i As Long
Const strName = "ddlServiceCat_1"
Const strWorkbookname As String = "C:\Path\Dropdown Entries List.xlsx"
    bCCtrl = False
    For Each oCC In ActiveDocument.ContentControls
        If oCC.Title = strName Then
            bCCtrl = True
            Exit For
        End If
    Next oCC
    If Not bCCtrl Then
        MsgBox "Content control not found!"
        Exit Sub
    End If
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlBook = xlApp.Workbooks.Open(Filename:=strWorkbookname)
    xlApp.Visible = True
    LastRow = xlBook.sheets(1).Range("A" & xlBook.sheets(1).Rows.Count).End(-4162).Row
    With oCC
        For i = 1 To LastRow
            .DropdownListEntries.Add xlBook.sheets(1).Range("A" & i)
        Next i
    End With
    Set xlBook = Nothing
    Set xlApp = Nothing
    Set oCC = Nothing
End Sub

[gmaxey]

If you want to fill display and value with different values then you will need to adapt Graham's code. I would use something like this:

Sub FillCC()
Dim oCC As ContentControl
Dim xlApp As Object
Dim xlBook As Object
Dim LastRow As Long, i As Long
Const strWorkbookname As String = "D:\DDL Entries.xlsx"
  On Error Resume Next
  Set oCC = ActiveDocument.SelectContentControlsByTitle("ddlServiceCat_1").Item(1)
  If oCC Is Nothing Then
    MsgBox "The CC you want to fill does not exist."
    GoTo lbl_Exit
  End If
  Set xlApp = GetObject(, "Excel.Application")
  If Err Then
    Set xlApp = CreateObject("Excel.Application")
  End If
  On Error GoTo Err_Handler
  Set xlBook = xlApp.Workbooks.Open(FileName:=strWorkbookname)
  xlApp.Visible = False
  LastRow = xlBook.sheets(1).Range("A" & xlBook.sheets(1).Rows.Count).End(-4162).Row
  With oCC
    .DropdownListEntries.Clear
    For i = 1 To LastRow
      .DropdownListEntries.Add xlBook.sheets(1).Range("A" & i), xlBook.sheets(1).Range("B" & i)
Record_Skip:
    Next i
  End With
  xlBook.Close wdDoNotSaveChanges
lbl_Exit:
  
  Set xlBook = Nothing
  Set xlApp = Nothing
  Set oCC = Nothing
  Exit Sub
Err_Handler:
  Debug.Print Err.Number
  Select Case Err.Number
    Case 6214 'Repeat entry.
      Resume Record_Skip
      'Or
      'Msgbox "Repeat entry. Terminating process"
      'oCC.DropdownListEntries.Clear
      'xlBook.Close wdDoNotSaveChanges
      'Resume lbl_Exit
    Case 1004
      MsgBox "The Excel file does not exist."
      Resume lbl_Exit
  End Select
End Sub

[richardray]

Thanks gents.

I managed to change the code to use the Content Control tag and to also use column B in the Excel file to map to the Value.

Once again, many thanks.....you saved me a lot of time!

[celias]

Dear Graham gmayor and Greg gmaxey, first of all, I want to thank you both for the solutions that you presented here. I was able to combine the two suggestions to populate a long drop-down list on a template that I am building in Word that has many entries, and also closing the excel file at the end. It just made my day! : )
To make this perfect I would need to get the word document to update the list of entries, as that is changed quite often. Do you have any suggestion to solve this?

Thank you in advance for any help you might provide.

[gmayor]

Save the document as a template add an autonew macro to the template to run the code whenever you create a new document from it.

[gmaxey]

As Graham implies, the CC refreshes anytime you run/re-run the code. So in addition to running it as a part of a AutonNew procedure, you could run with a AutoOpen procedure, assign it to a shortcut (keyboard, QAT, Ribbon) and run it whenever you like or even run it on entry to the CC.

[celias]

With oCC    .DropdownListEntries.Clear
            For i = 1 To LastRow
            .DropdownListEntries.Add xlBook.Sheets(1).Range("A" & i)
        Next i
    End With
    xlBook.Close wdDoNotSaveChanges

[celias]

Thank you so much, guys, for the suggestions. I ended up deciding not to run the macro every time the file is opened because copies of the file might be used by several people at the same time and when trying to get the data from the Excel file simultaneously it might create errors. Am I wrong?
So I entered a line to get the CC entries cleared before it is filled up with a new list and the macro will run only when requested. I will leave the little piece of code that I changed so that other people might use it in case they are looking for something similar.

Thank you so much, again!
May the VBA Awesomeness always be with you!

[gmaxey]

celias,

If your CC has a placeholder "Choose item" then instead of clearing the list you might want to do this instead:

   For i = .DropdownListEntries.Count To 2 Step -1
     .DropdownListEntries.Item(i.Delete
   Next lngItem