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! :thumb

[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