none
Populate Combobox in Excel from SQL Table RRS feed

  • Question

  • I have requirement to populate a combobox in excel from a table in SQL when the user opens the excel workbook. Can you help me on how I can do that. Please note this is not a userform in excel. I have placed the combobox on the worksheet.

    Excel Version 2010
    SQL Server Version 2008

    Thanks in advance

    Tuesday, September 23, 2014 12:06 PM

Answers

  • Hi Jaggy99,

    >>How can I used the same code by using ODBC DSN with readonly access to the sql server.<<

    Please try adding a new user DSN file through Control panel:

    Set the application inernt with "ReadOnly".

    After that, use the connection string as below:

    DSN=myDsn;Uid=myUsername;Pwd=;

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 24, 2014 6:15 AM
    Moderator
  • Hi Jaggy,

    >>But will this code still work after connecting to sql using odbc<<

    As I tested, the answer is yes.

    If you want to connect to Sql Server through ODBC, you only need to change the connection string as below:

    stConn = "Driver={SQL Server};Server=Server name;Database=database name;"

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 26, 2014 2:51 AM
    Moderator

All replies

  • Try to import the data you need to populate the Combobox in Excel.

    Below article will guide you how to accomplish this using ADO.

    How to import data from Microsoft SQL Server into Microsoft Excel

    Then with the data in Excel, you select the data from your sheet to populate the Combobox.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Tuesday, September 23, 2014 6:25 PM
  • Thanks Daniel but the code on the link you shared puts the data into a cell and I'm looking to populate a combobox on the worksheet.
    Tuesday, September 23, 2014 11:03 PM
  • Following the link posted by Daniel above and referring other forums I managed to populate the combobox. The code is as below. The issue with this I see is that Microsoft ActiveX Data Objects Library needs to enabled and a SQL login needs to be specified in the connection string.

    How can I used the same code by using ODBC DSN with readonly access to the sql server.

    Private Sub ComboBox1_DropButtonClick()

    'Declare Variables
        Dim cnt As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim stDB As String, stConn As String, stSQL As String
         
    'Set database connection string. This connects excel with SQL database
        Set cnt = New ADODB.Connection
            
            stConn = "Provider=SQLOLEDB.1;Password=xxx;Persist Security Info=True;User ID=sa;Initial Catalog=Training;Data Source=DEV\SDM2008R2"
        
        With cnt
            .Mode = adModeRead
            .CursorLocation = adUseClient
            .ConnectionString = stConn
            .Open
        End With
         
        Set rst = New ADODB.Recordset
         
    'SQL Query statement
            stSQL = "SELECT Name FROM P01"
            
            rst.Open stSQL, cnt
            ComboBox1.Clear
       
       Do While Not rst.EOF
            ComboBox1.AddItem rst(0)
            rst.MoveNext
       Loop

    'Release objects from memory.
        rst.Close
            Set rst = Nothing
        cnt.Close
            Set rst = Nothing

    End Sub

    Wednesday, September 24, 2014 4:29 AM
  • Hi Jaggy99,

    >>How can I used the same code by using ODBC DSN with readonly access to the sql server.<<

    Please try adding a new user DSN file through Control panel:

    Set the application inernt with "ReadOnly".

    After that, use the connection string as below:

    DSN=myDsn;Uid=myUsername;Pwd=;

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 24, 2014 6:15 AM
    Moderator
  • Thanks for the reply George. But will this code still work after connecting to sql using odbc

    'SQL Query statement
            stSQL = "SELECT Name FROM P01"
            
            rst.Open stSQL, cnt
            ComboBox1.Clear
       
       Do While Not rst.EOF
            ComboBox1.AddItem rst(0)
            rst.MoveNext
       Loop

    'Release objects from memory.
        rst.Close
            Set rst = Nothing
        cnt.Close
            Set rst = Nothing

    Thursday, September 25, 2014 11:47 AM
  • Hi Jaggy,

    >>But will this code still work after connecting to sql using odbc<<

    As I tested, the answer is yes.

    If you want to connect to Sql Server through ODBC, you only need to change the connection string as below:

    stConn = "Driver={SQL Server};Server=Server name;Database=database name;"

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 26, 2014 2:51 AM
    Moderator