Hello There,
I am using ADODB for extracting data from another excel workbook into a combobox. However the headers do not show up in the combobox. Therefore I came up with the following solution to include the header and '----' separator in the 1st and 2nd row. Then display the actual records.
The problem is that it gets all overwritten with the command:
ActiveSheet.ComboBoxDisplayBurger.Column = rst.GetRows
Below is the code for your reference. Could you please advise how can I retain the 1st 2 inserts?
Looking forward to hearing from you
Regards
Don
I am using ADODB for extracting data from another excel workbook into a combobox. However the headers do not show up in the combobox. Therefore I came up with the following solution to include the header and '----' separator in the 1st and 2nd row. Then display the actual records.
The problem is that it gets all overwritten with the command:
ActiveSheet.ComboBoxDisplayBurger.Column = rst.GetRows
Below is the code for your reference. Could you please advise how can I retain the 1st 2 inserts?
Looking forward to hearing from you
Regards
Don
Code:
cn.Open strCon
strQuery = "SELECT * FROM [Sheet1$A:R] WHERE [Party Name]='" & CBuyerName & _
"' AND [VAT Number]='" & CBuyerVATNumber & "';"
MsgBox strQuery
Set rst = New ADODB.Recordset
rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
Dim i As Integer
rst.MoveFirst
i = 0
With ActiveSheet.ComboBoxDisplayBurger
.Clear
Do
.AddItem
'Include Column Headings. It seems this is a bug that the column headings does not come auto therefore this workaround
If ColumnHeadingsCount = 0 Then
.List(i, 0) = "Party Name"
.List(i, 1) = "Pick-up"
.List(i, 2) = "Mode"
.List(i, 3) = "Empty Depot"
.List(i, 4) = "Local THC Rotterdam"
.List(i, 5) = "Gross Tonnage"
.List(i, 6) = "Terminal"
.List(i, 7) = "Transport"
'.List(i, 8) = "WHS Xdock"
'.List(i, 9) = "FTL"
ColumnHeadingsCount = 1
i = i + 1
rst.MoveNext
.AddItem
.List(i, 0) = "----------------------------------------------------------------------"
.List(i, 1) = "--------------------------"
.List(i, 2) = "----------------------------------------------------------------------"
.List(i, 3) = "------------------------------------------------------------"
.List(i, 4) = "--------------------------"
.List(i, 5) = "--------------------------"
.List(i, 6) = "--------------------------"
.List(i, 7) = "--------------------------"
i = i + 1
rst.MoveNext
.AddItem
End If
ActiveSheet.ComboBoxDisplayBurger.Column = rst.GetRows
Exit Sub