Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Using ComboBox to feed into another function

    How is it possible to take the choice from the Combo Box and send that into a Stored Procedure statement in Excel VBA

    I have 3 Combo Boxes, Months, Years, Date.

    I'm trying to fillout these into this statement to run my Stored Procedure...

    Code:
    EXEC sp_WLName_Report 'September', '2008', " & _
                                "'18-09-2008', 'C2PD'
    This is my full code that brings back the results

    Code:
    Option Explicit
    
    Dim cnnDW As ADODB.Connection
    Dim rsDW As ADODB.Recordset
    Dim cmdSP As ADODB.Command
    Dim strDWFilePath As String
    Dim stProcName As String
    
    Sub GetData()
        On Error GoTo Err:
    '-------------------------------------------------------------------------------------------------------------------------------------------------
    'Connects to CISSQL on SQL Server and Database CORPINFO, uses the connection of the network Username &
    'Password for the user edditing this report.
        strDWFilePath = "Driver={SQL Native Client};" & _
           "Server=CISSQL1;" & _
           "Database=CORPINFO;" & _
           "Trusted_Connection=Yes"
        Set cnnDW = New ADODB.Connection
    '-------------------------------------------------------------------------------------------------------------------------------------------------
        Application.ScreenUpdating = False
        cnnDW.Open strDWFilePath
        Set rsDW = New ADODB.Recordset
    '-------------------------------------------------------------------------------------------------------------------------------------------------
    'Define name of Stored Procedure to execute
        stProcName = "EXEC sp_WLName_Report 'September', '2008', " & _
                                "'18-09-2008', 'C2PD'"
    '-------------------------------------------------------------------------------------------------------------------------------------------------
        Sheet1.Range("E4:F9").ClearContents
        rsDW.CursorLocation = adUseClient
        rsDW.Open stProcName, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
        Application.ScreenUpdating = False
        Sheet1.Range("E4").CopyFromRecordset rsDW
        rsDW.Close
        Set rsDW = Nothing
    '-------------------------------------------------------------------------------------------------------------------------------------------------
        MsgBox "Import Complete", vbInformation, "SQL Connection"
        cnnDW.Close
        Set cnnDW = Nothing
        Exit Sub
    '-------------------------------------------------------------------------------------------------------------------------------------------------
    Err:
        MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error, vbCritical, "SQL Connection"
        MsgBox VBA.Err
    End Sub
    How is it possible to do this?

  2. #2
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    I think you can use:
    stProcName = "EXEC sp_WLName_Report sheet1.combobox1.value, sheet1.combobox2.value, " & _
    "sheet1.combobox3.value, 'C2PD'"
    supposing that the controls are on sheet1 and have the default names...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •