Get A List From Excel And Populate Into A Drop Down List Control In Word
Manually typing each option for a drop-down list in word (drop-down list content control) is time consuming, but there is no native way to import a list you’ve made in Excel, or exported from a database to a workbook… to Word.
Getting our List Set Up
There’s a couple things we have to do first:
Excel
- get your list into an excel workbook
- do not put a header in A1, your first drop-down option goes in A1
- your list options only in Column A
- save and close the workbook
Word
- create a combo box (drop-down) content control in Word, from the Developer tab
- select the combo box (drop-down) content control by clicking on the 3 dots on the top left
VBE Window (Word)
- VBA Reference to the Excel Object Model is required, via Tools → References → Microsoft Excel…
- rename the workbook path and sheet name to your requirements
- run the macro
'===================================================================================================
' ## Get a list from Excel and populate into a Drop Down List control in Word.
' Do not put a header in A1, put your first drop down option in A1 then fill down.
' Close the source workbook before operating. It is possible to have it open but then until Word
' is closed the workbook will open in Read-Only.
' Select the control by clicking on the 3 dots on the top left then run the macro.
' Rename the workbook path and sheet name to your requirements.
' VBA Reference to the Excel Object Model is required, via Tools|References -> Microsoft Excel...
'===================================================================================================
Sub DropDownFromExcel()
'// Vars
Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook
Dim wbName As String, shtName As String, LRow As Long, i As Long
'// Screen updating off
Application.ScreenUpdating = False
'// Workbook path
wbName = "C:\Book1.xlsx"
'// Sheet name
shtName = "Sheet1"
'// Test the workbook exists
If Dir(wbName) = "" Then
MsgBox "Cannot find the designated workbook: " & wbName, vbExclamation
Exit Sub
End If
'// Get the list from Excel, column A
With xlApp
.Visible = False
Set xlWkBk = .Workbooks.Open(FileName:=wbName, ReadOnly:=True, AddToMRU:=False)
With xlWkBk
With .Worksheets(shtName)
'// Find the last-used row in column A.
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'// Populate the selected content control with Column A cell values.
' note not to include a header in the spreadsheet.
Selection.Range.ContentControls(1).DropdownListEntries.Clear
For i = 1 To LRow
Selection.Range.ContentControls(1).DropdownListEntries.Add _
Text:=Trim(.Range("A" & i))
Next
End With
.Close Savechanges:=False
End With
.Quit
End With
'// Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
Application.ScreenUpdating = True
End Sub