none
How to populate a combo box in Excel from a SQL Server Stored Procedure ?

    Question

  • I have a stored procedure [RPT_Res_Entry_CCList].  I want this to populate the combo box 'cmbCC' in Excel on tab 'Entry'.  The stored procedure has two columns of data.  i want the first column to be in the drop down.  Can someone please help?
    MiltonCor
    Friday, December 30, 2011 9:24 PM

Answers

  • Populate a ComboBox?  Hummm...  I've ran StoreProcs from VBA before, to return an array from SQL Server.  I've never used VBA to populate a ComboBox based on a StoreProc...  Nevertheless, take a look at these 2 code samples, and see if you can modify either to suit your needs.

     

    Option Explicit

    Sub Working2()

    Dim con As Connection
    Dim rst As Recordset
    Dim strConn As String

    Set con = New Connection
    strConn = "Provider=SQLOLEDB;"
    strConn = strConn & "Data Source=(local)\SQLExpress;"
    strConn = strConn & "Initial Catalog=Northwind;"
    strConn = strConn & "Integrated Security=SSPI;"

    con.Open strConn

    'Put a country name in Cell E1
    Set rst = con.Execute("Exec dbo.CustOrdersOrders '" & ActiveSheet.Range("E1").Text & "'")

    'The total count of records is returned to Cell A5
    ActiveSheet.Range("A5").CopyFromRecordset rst

    rst.Close
    con.Close

    End Sub


    Sub Working()

    Dim con As Connection
    Dim rst As Recordset

    Set con = New Connection
    con.Open "Provider=SQLOLEDB;Data Source=(local)\SQLExpress;Initial Catalog=Northwind;Integrated Security=SSPI;"

    Set rst = con.Execute("Exec dbo.[CustOrderHist]" & "'" & ActiveSheet.Range("E1").Text & "'")
    'Results of SProc are returned to Cell A1
    ActiveSheet.Range("A1").CopyFromRecordset rst

    rst.Close
    con.Close
    End Sub

    Saturday, December 31, 2011 1:05 AM

All replies

  • Populate a ComboBox?  Hummm...  I've ran StoreProcs from VBA before, to return an array from SQL Server.  I've never used VBA to populate a ComboBox based on a StoreProc...  Nevertheless, take a look at these 2 code samples, and see if you can modify either to suit your needs.

     

    Option Explicit

    Sub Working2()

    Dim con As Connection
    Dim rst As Recordset
    Dim strConn As String

    Set con = New Connection
    strConn = "Provider=SQLOLEDB;"
    strConn = strConn & "Data Source=(local)\SQLExpress;"
    strConn = strConn & "Initial Catalog=Northwind;"
    strConn = strConn & "Integrated Security=SSPI;"

    con.Open strConn

    'Put a country name in Cell E1
    Set rst = con.Execute("Exec dbo.CustOrdersOrders '" & ActiveSheet.Range("E1").Text & "'")

    'The total count of records is returned to Cell A5
    ActiveSheet.Range("A5").CopyFromRecordset rst

    rst.Close
    con.Close

    End Sub


    Sub Working()

    Dim con As Connection
    Dim rst As Recordset

    Set con = New Connection
    con.Open "Provider=SQLOLEDB;Data Source=(local)\SQLExpress;Initial Catalog=Northwind;Integrated Security=SSPI;"

    Set rst = con.Execute("Exec dbo.[CustOrderHist]" & "'" & ActiveSheet.Range("E1").Text & "'")
    'Results of SProc are returned to Cell A1
    ActiveSheet.Range("A1").CopyFromRecordset rst

    rst.Close
    con.Close
    End Sub

    Saturday, December 31, 2011 1:05 AM
  • Instead of having the recordset drop to cell A1, I want the recordset to be a list when you click the combo box. 

    So if the sp query is SELECT * FROM Alphabet, when you click the dropdown list, you see letters A-Z as the choices.

    Can you help regarding that aspect?  thanks


    MiltonCor
    Monday, January 09, 2012 9:06 PM