Use SQL query output to populate a Combobox in Excel

  • First off, I'm new to VBA so if my 'problem' can be solved by reading a (simple) HowTo, please let me know...


    Ok, that said, here's my question:


    How do I populate a combobox with values stored in a table on a SQL server?


    1 Connect to a SQL Server
    2 Run a query

    SQL
    1. SELECT DISTINCT SapOrderNummer
    2. FROM "Dosing Data"
    3. WHERE SapOrderNummer>'0'
    4. Order by SapOrderNummer asc;


    3 Store the query results in an array


    4 Display array content in a combobox?


    Am I on the right track here or do I miss the point completly?

  • Re: Use SQL query output to populate a Combobox in Excel


    Here's an example.


    It requires.


    1. A reference to be set in the VBE to MDAC 2.5 or >
    2. A userform called frmdata with a listbox called ListBox1



    The listbox on the userform will be populated with the results of your SQL Server query.


    Obvuisly you will need to amend your connection details & SQL query to match your database/server details.


    Hope this helps....

  • Re: Use SQL query output to populate a Combobox in Excel


    Quote from Mesjefskie

    Thank you for your help! Is your example only applicable to a userform or can it be used in a module to?


    No, it's only my example that would require a userform.


    If the listbox was in sheet1, for example, you'd just need to replace this


    Code
    1. With frmData
    2. With .ListBox1
    3. .Clear
    4. .BoundColumn = k
    5. .List = Application.Transpose(vaData)
    6. .ListIndex = -1
    7. End With
    8. .Show vbModeless
    9. End With


    with this


    Code
    1. With Sheet1
    2. With .ListBox1
    3. .Clear
    4. .BoundColumn = k
    5. .List = Application.Transpose(vaData)
    6. .ListIndex = -1
    7. End With
    8. End With


    :)

  • Just refer to a combobox instead of a listbox. The code is otherwise the same.


    Also note that this thread is 15 years old, so the original participants are unlikely to be monitoring it!

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why