Run Stored Procedure and get contents in Excel VBA

excel-stored-procedure.vba

Const strDbConn As String = "server=server-goes-here;Database=db-goes-here;Trusted_Connection=Yes;Driver={ODBC Driver 17 for SQL Server}"
 
 
Private Function getInfoFromStoredProcedure() As ADODB.Recordset
 
    On Error GoTo Error:
 
    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_SPNameHere" ' Name of stored procedure
    adoCmd.ActiveConnection = adoCon
   
    'Add parameters to stored proc
    'adoCmd.Parameters.Append adoCmd.CreateParameter("@myParamHere", adVarChar, adParamInput, Len(myParam), myParam)
   
    'Open command to execute query and return recordset
    Set getInfoFromStoredProcedure = New ADODB.Recordset
    getInfoFromStoredProcedure.Open adoCmd
      
    Set adoCmd = Nothing
    Set adoCon = Nothing
    Exit Function
 
Error:
    Debug.Print "error"
    Exit Function
   
End Function
 
Public Sub getDataFromRecordset()
 
    Dim sheet As String
    sheet = "Final"
   
    Worksheets(sheet).Select
    Range("A1").Select
   
    Set rPrint = Worksheets(sheet).Range("A2")
   
    Dim rst As New ADODB.Recordset
   
    Set rst = getInfoFromStoredProcedure()
   
    If Not rst.EOF Then
        rPrint.CopyFromRecordset rst
    End If
   
    rst.Close
   
    
    Cells.Select
    Selection.ColumnWidth = 50.86
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
   
    MsgBox " Report downloaded"
   
End Sub