none
Passing Parameter from excel to stored procedure.

    Question

  • Hi,

    I have one SP in database say DB having 2 parameters (one is dropdown and other is single text value) , now i want to connect this sp from excel which have option for dynamic parameter, i mean user can provide value to the provide by selecting values from drop down or giving values in text box .

    Also, parameter value column in not included in the detail list of data.

    Please suggest.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Wednesday, August 01, 2012 9:04 AM

Answers

All replies

  • Hi,

    As this issue, I think maybe it can be done via vba code.

    Suppose you use SQLOLEDB Provider connecting to a MS SQL Server 2005 database.

    Then here is an example code:

    If you want to use dropdown list, then insert a combo box control, and in the code, we can use ComboBox1.Value in the code to pass parameter.

    Sub Macro1()

     With ActiveWorkbook.Connections("Query from test").ODBCConnection

      .BackgroundQuery = True

      .CommandText = Array( _

      "SELECT Address.AddressID, Address.City, Address.PostalCode" & Chr(13) & "" & Chr(10) & "FROM AdventureWorks.Person.Address Address" & Chr(13) & "" & Chr(10) & "WHERE City " _

      , "= ?")

      .CommandType = xlCmdSql

      .Connection = Array(Array( _

      "ODBC;DRIVER=SQL Server;SERVER=VICKY03-MSFT;UID=v-xugong;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=V-XUGONG-MSFT;DATABASE" _

      ), Array("=AdventureWorks"))

      .RefreshOnFileOpen = False

      .SavePassword = False

      .SourceConnectionFile = ""

      .SourceDataFile = ""

      .ServerCredentialsMethod = xlCredentialsMethodIntegrated

      .AlwaysUseConnectionFile = False

     End With

     With ActiveWorkbook.Connections("Query from test")

      .Name = "Query from test"

      .Description = ""

     End With

     ActiveWorkbook.Connections("Query from test").Refresh

    End Sub

    Quote from:

    http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/f9e7b292-b8f4-4d42-9b16-44df1140af93


    Jaynet Zhang

    TechNet Community Support

    Thursday, August 02, 2012 5:22 AM
    Moderator
  • Hi Jaynet,

    I did as below...

    1, First take combo box and write code as below

    ==============================================

    Public Sub ListBox1_Click()

    Dim lItem As Long
    Dim strSelected1 As String
    Dim strSelected As String
    For lItem = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(lItem) = True Then
    strSelected = strSelected & ListBox1.List(lItem) & ", "
    End If
    Next lItem

    Range("K1").Value = strSelected

    End Sub

    =================================================

    2. Create another macro and call this, code as below

    ================================================

    Sub Macrot()

    Call Sheet2.ListBox1_Click
    With ActiveWorkbook.Connections("Mail").OLEDBConnection
            .CommandText = " Mailing '" & Range("K1").Value & "','" & Range("H1").Value & "'"
        End With
        ActiveWorkbook.Connections("Mail").Refersh

    End Sub

    ====================================================== 

    3. One issue i am facing in this....

    Though above code working fine when i select any value in combo box and execute macro, it will generate result as disired..

    but i have issue on this , detail as below..

    --> Suppose i selected two values in combo box say A and B, it will generate result respect to A and B..but now if i deselect say B and execute Macro, it will show result of A and B , it should be of A only, i have also noted column K has been updated as A which was earlier A,B....also when i refresh the sheet manualy, it will show result for A only, that is what is required.

    What is believe i missed something in VBA, i am newbie on VBA.

    Please suggest


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Thursday, August 02, 2012 8:44 AM
  • check this link this may help you out

    http://blogs.office.com/b/microsoft-excel/archive/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba.aspx

    Thursday, August 09, 2012 11:20 AM
  • Just acheived by tweaking in VBA code, and finally going to close the ticket.

    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Tuesday, August 21, 2012 8:21 AM