none
Connecting my SQL query to Excel RRS feed

Answers

  • Hi mrg5299,

    You can create a ComboBox control in the Excel workbook, then populate all the possible keys in it. For how to add a ComboBox control to the workbook and populate data, see these two articles:

    Add a list box or combo box to a worksheet

    How to add data to a ComboBox control or a ListBox control in Excel or Word

    Then in the ComboBox's change event handler, use ADO to connect to SQL Server, for how to do this, please go through this article, code samples are provided there:

    How to import data from Microsoft SQL Server into Microsoft Excel

    A simple one here:

    Sub ConnectToSqlServer()
    	Dim conn As New ADODB.Connection
    	Dim cmd As New ADODB.Command
    	Dim sConnString As String
    	Dim recordsAffected as Long
    
    	sConnString = "Provider=sqloledb; Server=SERV; Database=db; Trusted_Connection=True;"
    
    	conn.Open sConnString
    	With cmd
    	  .ActiveConnection = conn
    	  .CommandType = adCmdText
    	  .CommandText = "INSERT INTO TBL (col1, col2) VALUES ('val', 'val');"
    	  .Execute recordsAffected
    	End With
    
    	If CBool(conn.State And adStateOpen) Then conn.Close
    	Set cmd = Nothing
    	Set conn = Nothing
    End Sub


    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, February 6, 2015 10:35 AM
    Moderator

All replies

  • Hi mrg5299,

    You can create a ComboBox control in the Excel workbook, then populate all the possible keys in it. For how to add a ComboBox control to the workbook and populate data, see these two articles:

    Add a list box or combo box to a worksheet

    How to add data to a ComboBox control or a ListBox control in Excel or Word

    Then in the ComboBox's change event handler, use ADO to connect to SQL Server, for how to do this, please go through this article, code samples are provided there:

    How to import data from Microsoft SQL Server into Microsoft Excel

    A simple one here:

    Sub ConnectToSqlServer()
    	Dim conn As New ADODB.Connection
    	Dim cmd As New ADODB.Command
    	Dim sConnString As String
    	Dim recordsAffected as Long
    
    	sConnString = "Provider=sqloledb; Server=SERV; Database=db; Trusted_Connection=True;"
    
    	conn.Open sConnString
    	With cmd
    	  .ActiveConnection = conn
    	  .CommandType = adCmdText
    	  .CommandText = "INSERT INTO TBL (col1, col2) VALUES ('val', 'val');"
    	  .Execute recordsAffected
    	End With
    
    	If CBool(conn.State And adStateOpen) Then conn.Close
    	Set cmd = Nothing
    	Set conn = Nothing
    End Sub


    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, February 6, 2015 10:35 AM
    Moderator
  • Hi mrg5299,

    Please let us know whether if you still have problems about this issue.

    I would close this thread templary since the origanl issue was resolved. In addition, we don't recommend remove the orignal post so that if others who have the same issue could get benifit from the thread.

    Thanks for your understanding.

    Regards & Fei


    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.

    Monday, February 16, 2015 8:35 AM
    Moderator