Import list from Excel into drop-down content control in word

[shanshan89]

Hi!



I realized if the excel (where we import the excel column as a simple word dropdown list) has "blank" fields which could be due to users deleting text from a cell instead of deleting the entire row, there may be a "Runtime Error 94: Invalid use of NULL," when we open the word macro document.

Instead of writing a macro in excel to delete all blank fields, is there a method where we can manipulate the vba code in word to ignore all blank fields from the excel list?

I'm using a sample code from Mr Gregory's webpage "https://gregmaxey.com/word_tip_pages/import_excel_list_into_word_dropdownlist.html" as shown below:


Private Function fcnExcelDataToArray(strWorkbook As String, _
Optional strRange As String = "Sheet1", _
Optional bIsSheet As Boolean = True, _
Optional bHeaderRow As Boolean = True) As Variant
'Default parameters include "Sheet1" as the named sheet, range of the full named sheet _
and a header row is used.
Dim oRS As Object, oConn As Object
Dim lngRows As Long
Dim strHeaderYES_NO As String
strHeaderYES_NO = "YES"
If Not bHeaderRow Then strHeaderYES_NO = "NO"
If bIsSheet Then strRange = strRange & "$]" Else strRange = strRange & "]"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12 .0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=" & strHeaderYES_NO & """;"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open "SELECT * FROM [" & strRange, oConn, 2, 1
With oRS
.MoveLast
lngRows = .RecordCount
.MoveFirst
End With
fcnExcelDataToArray = oRS.GetRows(lngRows)
lbl_Exit:
If oRS.State = 1 Then oRS.Close
Set oRS = Nothing
If oConn.State = 1 Then oConn.Close
Set oConn = Nothing
Exit Function
End Function


Sub Document_Open()
Dim strWorkbook As String
Dim lngIndex As Long
Dim arrData As Variant
Dim oCC As ContentControl, oFF As FormField
Dim bReprotect As Boolean
Application.ScreenUpdating = False
'The Excel file defining the simple list. Change to suit.
strWorkbook = ThisDocument.Path & "\Excel Data Store.xlsx"
If Dir(strWorkbook) = "" Then
MsgBox "Cannot find the designated workbook: " & strWorkbook, vbExclamation
Exit Sub
End If
'Get the data. Change sheet name to suit.
arrData = fcnExcelDataToArray(strWorkbook, "Simple List")
Set oCC = ActiveDocument.SelectContentControlsByTitle("CC Dropdown List").Item(1)
'Populate the CC
If oCC.DropdownListEntries.Item(1).Value = vbNullString Then
'Assumes the CC has a placeholder "Choose Item" entry with no defined value. _
Preserve the placeholder entry.
For lngIndex = oCC.DropdownListEntries.Count To 2 Step -1
oCC.DropdownListEntries.Item(lngIndex).Delete
Next lngIndex
Else
'Assumes no placeholder entry. Simple clear list.
oCC.DropdownListEntries.Clear
End If
For lngIndex = 0 To UBound(arrData, 2)
oCC.DropdownListEntries.Add arrData(0, lngIndex), arrData(0, lngIndex)
Next

lbl_Exit:
Application.ScreenUpdating = True
Exit Sub
End Sub


Thanks!