Using Stored Procedure as Data Source in Excel

When you want to use a SQL Server as external data source in excel, you can find, that it is not possible to select a stored procedure as a source of data in the Data Connection Wizard. This can lead to a conclusion, that it’s not possible to use a Stored Procedure as a source of data in Excel.

Excel Data Connection Wizard

Although, you cannot select a stored procedure, in the Data Connection Wizard, there is a simple workaround who to select a stored procedure as a source of data in Excel.

First, create a connection using the wizard and select any table as a source for the newly created connection. Once the connection is created, choose properties for the connection and switch to the Definition Tab.

Workbook Connections & Connection Properties

In the Definition type, change the Command Type to SQL and as Command text put the EXEC statement to execute the stored procedure. Once you enter, the Exec command, and confirm the dialog, you have fully configured  connection to SQL Server which source is a Stored Procedure.

Excel Connection Properties

On the dialog confirmation a warning message appears telling you, that the connection in Excel and the external connection files doesn’t match and that the link will be removed. Simply confirm the message by Yes as the connection will be stored in the excel workbook.

Confirmation of removing link to a connection file

Such modified connection you can use for importing data to Excel sheet, pivot table, pivot graph etc..

Advertisements

21 thoughts on “Using Stored Procedure as Data Source in Excel

    • Hi,

      if you need a static parameter, then you could pas it as part of the command text in the dialog mentioned in the post.

      In case of dynamic passing I would use a VBA to dynamically update command text of the Connection.
      So when you need to pass a new value you could call a Sub or Function with something similar to the code below:

      Dim c As WorkbookConnection
      Dim param As String
      
      param = "12"
      Set c = ThisWorkbook.Connections.Item("Moje")
      c.OLEDBConnection.CommandText = "EXEC dbo.usp_TestProc " & param
      c.Refresh();
      
  1. I found that I had to also put “set nocount on” into the stored procedure that I was calling. Once I did this I was able to view the data in Excel.

    • Hi, good point.

      If there is no SET NOCOUNT ON in the store procedure, than additional record set is returned with the count of records being processed by the stored proc. This can cause problem.

      Anyway it is a best practice to include the SET NOCOUNT ON in the stored procedures.

  2. I need to pass two date parameters to my stored procedure. I enter the two dates into cells B3 and B4 on the spreadsheet and then have the following macro to refresh the data based on the entered date parameters.

    Sub RefreshQuery()

    With ActiveWorkbook.Connections(“Availability by Date Range”).OLEDBConnection
    .CommandText = “EXECUTE sp_BP_Availability_DateRange ‘” & Range(“B3”).Value & “‘, ‘” & Range(“B4”).Value & “‘”
    End With
    ActiveWorkbook.Connections(“Availability by Date Range”).Refresh

    End Sub

    However, this returns a ‘error returning data type varchar to date’ error. I have tried formatting cells B3 and B4 as both text and date but get the same result. My VBa knowledge is limited so I could well be doing something stupid.
    Any suggestions?

  3. I came here for the part in your first comment reply where you explain how to programmatically change the parameters or the query of an Excel Connection.

    We generate reports from a scheduler built in Access, and we change the datesFrom and dateTo of our reports really often.

    With your explanations, I built a really simple Sub that you can call from any place that you wish to change your Excel connection’s query from an external place.

    Here it is : (Translated from french since I’m french 😉

    Public Sub updateExcelConnectionQuery(path As String, connectionName As String, query As String)
    Dim xlApp As New Excel.Application
    Dim xlDoc As Excel.Workbook

    Set xlDoc = xlApp.Workbooks.Open(path)
    xlDoc.Connections.item(connectionName).OLEDBConnection.CommandText = query

    xlDoc.RefreshAll
    xlDoc.Save
    xlDoc.Close
    End Sub

    Thank you

  4. There is another way to pass parameters, a hint that it is possible is visible in your screenshot of connection properties where there is a Parameters… button greyed out. Simply select the From other Sources dropdown and pick the Microsoft Query option at the bottom instead of SQL Server. Don’t bother with the query builder, click the SQL button and put your query there. This supports parameters as question marks in the text, when you run it you are prompted for the parameters in turn and can enter a value or a cell reference then you can save the values.

    This has it’s own quirks, I couldn’t run a TOP 100 and it’s very fussy about the parameter formats, but it works. I haven’t tried referencing a stored procedure yet.

  5. I was able to pass 7 filters to a stored procedure using your method above. I filter the 1.7 million records before it has to be downloaded. My only issue is that when I publish to our SharePoint 2013 gallery it it not friendly – forcing the user to download to Excel (2010 in our case). How can I resolve this issue? Thanks.

    • Hi, what you mean by “forcing the user to download to Excel”? You mean, that you deploy the excel to SharePoint, then user downloads that excel, but has to refresh the rows?

  6. whenever i open, does the data refresh automatically?
    we have an option in data connection properties, but just want to know whether this process do the same

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s