VBA populate sql query into ListBox directly into the spreadsheet (no userform)

Question

So I checked some examples and it seems there are a lot of peoples that populate forms from SQLinto excel but in my case, I have a multi selection Excel listBox that is linked with a Sql recordset. Basically, I managed to send my full SQL recordset into my spreadsheet. It's a table (1500 rows,9 columns) and in my spreadsheet, Above this generated table I have 9 Listboxes, each Listbox should represents all the entries of its linked column. I want to start simple and populate one column into my excel Listbox so that users can just select whatever entry(ies) they want into the listbox. My problem is that I don't find the right ListBox method to display my entries. Here is my code so far:

Populating the recordset into SQL (That is working for whom who need this):

Sub Get_Datas_From_SQL()

    Dim mobjConn As ADODB.Connection
    Dim strConn As String
    Set mobjConn = New ADODB.Connection
    Dim strSQL As String
    
    strConn = "Provider=SQLOLEDB; Data Source=My_server;" _
               & "Initial Catalog=My_db;Integrated Security=SSPI;"
    
    mobjConn.Open strConn
    
    Dim rs As ADODB.Recordset
    Dim Rn As Range
    Set rs = New ADODB.Recordset
    Set Rn = My_sheet.Range("A20")

    My_sheet.Range(Rn, Rn.Offset(2000, 20)).ClearContents
    
    strSQL = "SELECT * FROM Stocks_table"
      
    With rs
        .ActiveConnection = mobjConn
        .Open strSQL
        Rn.CopyFromRecordset rs
        .Close
    End With
    
    mobjConn.Close
    
    Set rs = Nothing
End Sub

Now, as a start, I add the code that is supposed to populate one of the nine Listboxes

Sub init_()

    Dim mobjConn As ADODB.Connection
    Dim strConn As String
    Set mobjConn = New ADODB.Connection
    Dim strSQL As String
    
    strConn = "Provider=SQLOLEDB; Data Source=My_server;" _
               & "Initial Catalog=My_db;Integrated Security=SSPI;"
    
    mobjConn.Open strConn
    
    strSQL = "SELECT DISTINCT Currency FROM Stocks_table "
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    With rs
        .ActiveConnection = mobjConn
        .Open strSQL
         Dim a()
         a = rs.GetRows
        ?
        .Close
    End With
End Sub

I took some remarks into account from previous questions and I'll get back to potential answers a fast as possible !

Thanks a lot in advance and have a great day

FunThomas · Accepted Answer · 2020-07-23 09:17:23Z

1

When you use the GetRows-method of a recordset, the data will be put into a 2-dimensional array. What's a little bit counter-intuitive is that the first index is the index into fields and the second is the index into the rows of the recordset.

You can assign a 2-dimensional array to the List-property of a listbox - but the first index needs to be the row and the second the field number (if you deal with a multi-column listbox). So all you need to do is to transpose the array before assigning it:

 a = rs.GetRows
 shtEquity.ListBoxCcy.List = Application.WorksheetFunction.Transpose(a)

answered Jul 23, 2020 at 9:17

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pierre_CM Over a year ago

Nice one it works perfectly like this if you replace your line of code by my For each loop. Just to be clear @FunThomas, when you mention "Fields", you mean column right ?

FunThomas Over a year ago

Yes, Columns it probably the better term.

Pierre_CM Over a year ago

Great got it, so that means that now, If I want to get my all recordset of 9 columns, I can simply assign each column (field) to a listbox ?

Pierre_CM · Accepted Answer · 2020-07-23 09:02:20Z

0

For those who want to know, here is the answer:

Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset

With rs
    .ActiveConnection = mobjConn
    .Open strSQL
     Dim a
     a = rs.GetRows
     For Each Row In a
       shtEquity.ListBoxCcy.AddItem Row
     Next
    .Close
End With

answered Jul 23, 2020 at 9:02

Pierre_CM

551 silver badge8 bronze badges

Comments

Dy.Lee · Accepted Answer · 2020-07-23 10:16:12Z

0

a = Rs.GetRows
Worksheets(1).Shapes(1).ControlFormat.List = a 'control ListBox
Worksheets(1).OLEObjects(1).Object.List = WorksheetFunction.Transpose(a) 'Oleobject listbox

if your Listbox is MSForms.ListBox then refer below.

Dim Ws As Worksheet
Dim oleObjt As MSForms.ListBox

Set Ws = Worksheets(1)
Set oleObjt = Ws.OLEObjects("ListBox1").Object 'Ws.OLEObjects(1).Object
oleObjt.Clear
oleObjt.List = WorksheetFunction.Transpose(a)

Control & Oleobject image

edited Jul 23, 2020 at 10:16

answered Jul 23, 2020 at 9:25

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

3 Comments

Pierre_CM Over a year ago

Hi, thanks for answering, I tried this but I might misunderstood something because it rendered me some error (collection index out of range): shtEquity.Shapes(ListBoxCcy).ControlFormat.List = a shtEquity.OLEObjects(ListBoxCcy).Object.List = WorksheetFunction.Transpose(a)

Dy.Lee Over a year ago

@Pierre_CM, i improve answer.

Pierre_CM Over a year ago

Hi @Dy.Lee, thank you so much for helping ! It's much more clear to me now

Collectives™ on Stack Overflow

VBA populate sql query into ListBox directly into the spreadsheet (no userform)

3 Answers 3

3 Comments

Comments

Control & Oleobject image

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Control & Oleobject image

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related