Excel run stored procedure single function

excel-run-stored-procedure-single-function.vba

Private Function getInfoFromStoredProcedure()
 
    Dim sheet As String
    sheet = "Data"
   
    Worksheets(sheet).Select
    Range("A1").Select
   
    Set rPrint = Worksheets(sheet).Range("A2")
    
    Dim adoCon As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim rst As New ADODB.Recordset
   
    Set adoCon = New ADODB.Connection
    Set adoCmd = New ADODB.Command
   
    'Create and open connection
    adoCon.ConnectionString = strDbConn
    adoCon.Open
   
    'Create command & link to stored proc & connection
    adoCmd.CommandType = adCmdStoredProc
    adoCmd.CommandText = "sp_StoredProcedureName" ' Name of stored procedure
    adoCmd.ActiveConnection = adoCon
   
    'Add parameters to stored proc
    'adoCmd.Parameters.Append adoCmd.CreateParameter("@myParam", adVarChar, adParamInput, Len(myParam), myParam)
   
    'Open command to execute query and return recordset
    rst.Open adoCmd
    
    Set adoCmd = Nothing
    Set adoCon = Nothing
    Exit Function
 
Error:
    Debug.Print "error"
    Exit Function
   
End Function