Running SQL Stored Procedure From Excel VBA Using Parameters From EXcel

Question

I'm trying to run below vba but unfortunately when i add the parameters range its not working (doing nothing without any errors), but if i used the code with simple select it work perfectly .. please help !

    Dim HRCONN As ADODB.Connection
    Dim HRDATA As ADODB.Recordset
    Dim HRFIELD As ADODB.FIELD

    Set HRCONN = New ADODB.Connection
    Set HRDATA = New ADODB.Recordset

    HRCONN.connectionstring = CONSTRSQL
    HRCONN.Open

    On Error GoTo closeconnection

    With HRDATA
    .ACTIVECONNECTION = HRCONN

    .Source = "EXEC DBO.LATENCY_REPORT" & "'" & Range("M12").Value & "'" & "," & "'" & Range("M13").Value & "'" & "," & "'" & Range("B12").Value & "'"""


    .locktype = adLockReadOnly
    .cursortype = adopenforwardonly
    .Open
    End With

    On Error GoTo closerecordset

    Worksheets.Add

    For Each HRFIELD In HRDATA.Fields
        ActiveCell.Value = HRFIELD.Name
        ActiveCell.Offset(0, 1).Select
    Next HRFIELD

    Range("A1").Select
    Range("A2").CopyFromRecordset HRDATA
    Range("A1").CurrentRegion.EntireColumn.AutoFit

    On Error GoTo 0
closerecordset:
    HRDATA.Close
closeconnection:
    HRCONN.Close
End Sub

Can you tell me where you are adding parameters in code ?

Prabhat Srivastava
– Prabhat Srivastava

2014-05-11 13:50:02 +00:00
Commented May 11, 2014 at 13:50 — Prabhat Srivastava
– Prabhat Srivastava, Commented May 11, 2014 at 13:50

Vladimir Semashkin · Accepted Answer · 2014-05-11 11:23:46Z

1

I have never done like that with VBA, but I did it with UI. So, first that I thing is query type. The second, I do not see any "," betweeen params.

answered May 11, 2014 at 11:23

Vladimir Semashkin

1,2801 gold badge10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vladimir Semashkin Over a year ago

You can set your query to some cell to see result of string concatenation and may be try to execute it with Managment Studio

Collectives™ on Stack Overflow

Running SQL Stored Procedure From Excel VBA Using Parameters From EXcel

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related