plifshits
03-07-2024, 04:30 AM
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!
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!