Excel VBA Fill Combo Box With Data From SQL Query

Question

I'm looking for some advice on how to fill in a combo box/list box with data that's derived from a SQL by VBA.

Sub RunSELECT()
Dim cn As Object, rs As Object, output As String, sql As String

'---Connecting to the Data Source---
Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = ""
    .Open
End With


'---Run the SQL SELECT Query---
sql = "SELECT DISTINCT i.* FROM tblITEMS i INNER JOIN tblITEM_ATTRIBUTE_VALUES iav1 on i.PART_NO = iav1.PART_NO and i.REV = iav1.rev and iav1.ATTRIBUTE_NAME = 'Item' AND iav1.ATTRIBUTE_VALUE = 'X100' INNER JOIN tblITEM_ATTRIBUTE_VALUES iav2 on i.PART_NO = iav2.PART_NO and i.REV = iav2.rev and iav2.ATTRIBUTE_NAME = 'Item Name' AND iav2.ATTRIBUTE_VALUE = 'Variable' ORDER BY i.PART_NO, i.REV"

Set rs = cn.Execute(sql)

Do
   output = output & rs(0) & ";" & rs(1) & ";" & rs(2) & vbNewLine
   Debug.Print rs(0); ";" & rs(1) & ";" & rs(2)
   rs.MoveNext
Loop Until rs.EOF

MsgBox output

'---Clean up---
rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub

The above creates a msgbox that works, and gives me information. I can export this 'output' to a cell but the cell contents should fill in multiple cells, not just one.

How can I alter my code to export 'output' to either multiple cells (as many as is needed) or into a combo/list box?

Cheers in advance

Hi Steusloff - thanks for your assistance, I'll add it to my reading list! Really need to brush up on my VBA skills! — user9035021

Rory Rory · Accepted Answer · 2017-11-30T17:01:22

To output to a combobox or listbox, replace this part:

Do
   output = output & rs(0) & ";" & rs(1) & ";" & rs(2) & vbNewLine
   Debug.Print rs(0); ";" & rs(1) & ";" & rs(2)
   rs.MoveNext
Loop Until rs.EOF

with this:

listboxname.Column = rs.GetRows

Excel VBA Fill Combo Box With Data From SQL Query

1 Answers