Can't populate Word VBA Combobox Value with last cell from xldown error

[plifshits]

Hello all,

I have Word VBA Macros for auto-fill document purposes.

I have a Combobox named CountryBox that should populate some Content Control fields based on value selection (taken from external Excel table).

I have it working for strict range of values (A1:A3) but every time I try to use 'xldown' to utilize all cells, VBA gives me error 438:
Object doesn't support this property or method


This is my code:

Private Sub UserForm_Initialize()
    'Populate CountryBox Combobox with Countries.xlsx
    Dim appExcel As Object
    Dim ExcelBook As Object
    Dim ExcelSheet As Object
    Dim ListItems As Variant, i As Integer
    Dim LastRow As Long
    On Error Resume Next
    Set appExcel = GetObject(Class:="Excel.Application")
    On Error GoTo 0
    If appExcel Is Nothing Then
         Set appExcel = CreateObject(Class:="Excel.Application")
         appExcel.Visible = True
    End If
    With Me.CountryBox
         .Clear ' remove existing entries from the listbox
         ' turn screen updating off,
         ' prevent the user from seeing the source workbook being opened
         Application.ScreenUpdating = False
         ' open the source workbook as ReadOnly
         Set ExcelBook = appExcel.Workbooks.Open("C:\Users\plifshits\Desktop\temaplte\Countries.xlsx", False, True)
         'ListItems = ExcelBook.Worksheets(1).Range("A1:A100").Value
         LastRow = appExcel.Cells(1, appExcel.Row.Count).End(appExcel.xlDown).Row
         ' With ExcelBook.Worksheets(1)
             ' ListItems = appExcel.Range(appExcel.Cells(1, 1), appExcel.Cells(appExcel.Rows.Count, 1).End(appExcel.xlDown)).Value
         ' End With
         ' get the values you want
         ExcelBook.Close False ' close the source workbook without saving changes
         Set ExcelBook = Nothing
         ListItems = appExcel.WorksheetFunction.Transpose(ListItems)
         ' convert values to a vertical array
         For i = 1 To UBound(ListItems)
             .AddItem ListItems(i) ' populate the listbox
         Next i
         .ListIndex = -1 ' no items selected, set to 0 to select the first item
         Application.ScreenUpdating = True
    End With
End Sub

Please advise!