I’m using the below function in an Excel macro to get the results of a simple stored procedure into an Excel spreadsheet. The function needs the connection string defined within it, but this can easily setup to be dynamic in the function parameters if needed. Also I’m only using one parameter, but I have no doubt that you create a multi-value function parameter to take several stored proc parameters.
In your macro, simply call the function and it will populate cells with your stored proc results beginning at the current active cell.
Function insertStoredProcValues(spName As String, strParameter As String)
Dim Conn As ADODB.Connection
Dim ADODBCmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim i As Integer
Dim sConnect As String
'--DEFINE CONNECTION STRING HERE---------------------------------------------------------
sConnect = "driver={sql server}; server=MYSERVERNAME; Database=MYDATABASENAME; UID=MYUSERNAME; PWD=MYPASSWORD;"
'----------------------------------------------------------------------------------------
'Establish connection
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
'Open recordset
Set ADODBCmd = New ADODB.Command
ADODBCmd.ActiveConnection = Conn
ADODBCmd.CommandText = spName
ADODBCmd.CommandType = adCmdStoredProc
ADODBCmd.Parameters.Refresh
ADODBCmd.Parameters(1).Value = strParameter
Set rs = ADODBCmd.Execute()
'Loop through recordset and place values
rs.MoveFirst
Do While rs.EOF = False
For i = 0 To rs.Fields.Count - 1
ActiveCell.Value = rs.Fields(i).Value 'insert value into cell
ActiveCell.Offset(0, 1).Activate 'move to next cell for next value
Next i
ActiveCell.Offset(1, -i).Activate 'move to next row for next record
rs.MoveNext
Loop
'Clean up
rs.Close
Set rs = Nothing
End Function
