Get data from a SQL Server Stored Procedure (Without Parameters) into Excel


I wish to Get data from a SQL Server Stored Procedure (Without Parameters) into Excel

Useful links as guidance >

However, I am unable to get the data to be returned.

Below are the necessary details -

Database Name – ZZ_Common

SP Name – Update_Vol

Can anyone help me with this?

Just wanted to know what changes should I do to the ?,? part when I have no parameters at all?

Deep Dave

Hi, One method I use is Powershell to move data from SQL Server. the main advantage is the granular control over Excel objects. This post offers an explanation and sample script

HI Jack,

Thank you for the reply.

However, I am trying to get the Stored Proc to work through Excel only.. I am avoiding VBA or any other codes for that matter..

Can you suggest how my SQL Statement should look like? I guess that should do it.. :smile:


I've done it using an ODBC connection to the database and just linking a SHEET in Excel to the SQL Sproc. User can press REFRESH on that linked tab and then cut & paste data that they want (or directly reference the cells from other sheets).

We use parameters too - in a separate "parameters" sheet

Ages since I've done it hence I'm a bit hazy on the details, but it might point you in the right direction. If not ask again and I'll see if I can dig out some details. Its definitely a "string and chewing gum" solution though.

1 Like

@deepDave - do you want to create a new worksheet each time or keep adding data to a worksheet?

1 Like

Thank you for the help..

I have managed to get it working.. :slight_smile:

This is a example, I hope it helps you:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim oActiveWorkbook As Workbook, oNewWorkbook As Workbook
Dim conn, cmd, rs, str As String, i As Integer, strLenguajeAccess As String
Dim strTD As String, strWS As String, intLastCol As Integer, cColName, strDA As String

Set conn = CreateObject("ADODB.Connection")

conn.ConnectionString = "DSN=YourOBC;Description=YourDescription;Trusted_Connection=Yes;APP=YourApp;"

Set rs = CreateObject("ADODB.Recordset")
str = "exec [dbo].[YourStoreProcedure] @Parameter1=N'" & Replace(Trim(Target.Value), "'", "''") & "', @Parameter2=2"

rs.Open str, conn, adOpenStatic, adLockReadOnly

If Not IsEmptyRecordset(rs) Then
    Set oActiveWorkbook = ActiveWorkbook
    Set oNewWorkbook = ActiveWorkbook
    oNewWorkbook.Application.Visible = False
    Sheets(ActiveSheet.Name).Name = "Data"
    i = 0
    For Each fld In rs.Fields
        oNewWorkbook.ActiveSheet.Cells(1, i + 1).Value = rs.Fields.Item(i).Name
        i = i + 1
    Next fld
    oNewWorkbook.ActiveSheet.Range("A2").CopyFromRecordset rs

    With oNewWorkbook
        .Application.Visible = True
    End With
    MsgBox "DonΒ΄t open store procedure.", _
End If

Set rs = Nothing

Set conn = Nothing

End Sub