Excel VBA: Execute SQL Server Stored Procedure for Each Row Based on Cell Values as Parameters

I am in the process of creating an excel macro that calls a SQL Server stored procedure and populates a column in a spreadsheet.

There are three columns in the spreadsheet: "Student Name", "Course Title" and "Enrollment Status". I want to be able to use "Student Name"  and "Course Title" cell values as parameters for the stored procedure.

The stored procedure is going to evaluate every row in the spreadsheet for that "Student Name" and "Course Title" (row combination) and populate the enrollment status in the "Enrollment Status" column.

This is a simple Stored Procedure that is querying a small data set so I am not worried about performance.

Note: This is conceptually identical to what I am trying to accomplish however I do not want to display business specific subject matter.

Example: A1 = Janet Smith, B1 = Physics. A1 and B1 are parameters for the stored procedure which then returns C1 that indicates enrollment status. I.E. "Janet Smith", "Physics", "Not
                          Registered"
                 A2 = Samuel Adams, B2 = Biology. A2 and B2 are parameters for the stored procedure which then returns C2 that indicates enrollment status. I.E. "Samuel Adams", "Biology",
                        "Registered"

This is what I came up with to take a single cell as a parameter. My questions is how can I do this for a range of cells (A1:A100, B1:100, C1:100).

Thanks in advance for your taking the time to look at this. Much appreciated.

Private Sub CommandButton1_Click()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String

Set cn = New ADODB.Connection

strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "SERVER=SQLABCD;INITIAL CATALOG=DBXYZ;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
cn.Open strConn

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "usp_SampleProc"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

'only have single set of parameters so far. Need to execute stored procedure for all rows
cmd.Parameters(1).Value = Range("A1")
cmd.Parameters(1).Value = Range("B1")

Set rs = cmd.Execute()

If Not rs.EOF Then
Worksheets("Enrollment").Range("C1:C100").CopyFromRecordset rs
rs.Close

Else
MsgBox "No data."
End If

End Sub
Sample_Spreadsheet.xlsx
Rob M.Asked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
So you want to execute the stored procedure for each row of data, taking the parameters from column A and B and putting the result in column C?

Perhaps something like this then.
Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String
Dim I As Long

    Set cn = New ADODB.Connection

    strConn = "PROVIDER=SQLOLEDB;"
    strConn = strConn & "SERVER=SQLABCD;INITIAL CATALOG=DBXYZ;"
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    cn.Open strConn

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandText = "usp_SampleProc"
    cmd.CommandType = adCmdStoredProc

    With Sheets("Sheet1")

        For I = 2 To .Range("A" & Rows.Count).End(xlUp).Row

            'only have single set of parameters so far. Need to execute stored procedure for all rows
            cmd.Parameters(1).Value = Range("A" & I).Value
            cmd.Parameters(1).Value = Range("B" & I).Value
            cmd.Parameters.Refresh

            Set rs = cmd.Execute()

            If Not rs.EOF Then
                Worksheets("Enrollment").Range("C1" & I).Value = rs.Fields(0)
                rs.Close
            Else
                MsgBox "No data."
            End If
        Next I

    End With

End Sub

Open in new window

0
 
Rob M.Author Commented:
Norie. Thanks very much! This was very helpful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.