Using Stored Procedure as Data Source in Excel

2011-01-04 Pavel Pawlowski Excel, SQL ServerConnection, Excel, SQL Server, Stored procedure

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.

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.

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.

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.

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

22 thoughts on “Using Stored Procedure as Data Source in Excel”

Tarvirdi says:

2011-05-29 at 10:48

Good advise,
But what about parameter passing? can you help me?
Thanks

Reply
- Pavel Pawlowski says:
  
  2011-05-29 at 14:17
  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();
```
  Reply
  - Kalpesh Hingu says:
    
    2011-07-11 at 15:14
    
    Great its working…
    
    Reply
Kalpesh Hingu says:

2011-07-11 at 15:13

Great its working

Reply
Magda says:

2012-08-09 at 13:08

Many thanks for this tutorial!

Reply
Matthew Maudsley says:

2012-11-09 at 22:51

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.

Reply
- Pavel Pawlowski says:
  
  2012-11-09 at 22:56
  
  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.
  
  Reply
- Matt says:
  
  2014-06-13 at 19:42
  
  VERY good save! Thank you.
  
  Reply
- Charubala says:
  
  2016-08-30 at 10:32
  
  It worked for me after handling this in SP. Thanks.
  
  Reply
Tony Hebditch says:

2013-08-21 at 14:19

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?

Reply
- Pavel Pawlowski says:
  
  2013-08-23 at 06:38
  
  Hi,
  
  try to format the values in format yyyMMdd which is language neutral and should always work in in SQL Server.
  
  so instead of Range("B3").Value you will use Format(Range("A1").Value, "yyyyMMdd"). This should solve the issue with string conversion to DateTime in the underlying SQL Server database engine.
  
  Reply
  - Tony Hebditch says:
    
    2013-08-23 at 10:24
    
    Pavel, Thanks a lot. That works a treat now.
    
    Reply
    - Pavel Pawlowski says:
      
      2013-08-23 at 11:35
      
      Great. 🙂
      
      Reply
Cofporation says:

2013-09-13 at 15:29

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

Reply
Colin Allen says:

2014-07-25 at 21:16

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.

Reply
Fred Stimler says:

2015-08-10 at 20:47

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.

Reply
- Pavel says:
  
  2015-08-11 at 06:45
  
  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?
  
  Reply
JM says:

2015-10-22 at 21:02

Awesome work-around. Thank you for posting! Helped me out of a jam today!!!

Reply
Sunny says:

2017-11-14 at 15:05

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

Reply
- Pavel Pawlowski says:
  
  2017-11-14 at 19:27
  
  It should behave the same way as any other data connection. You are only changing some properties of the standard data connection.
  
  Reply
Sunny says:

2017-11-14 at 16:40

is it refreshable?

Reply
chris says:

2020-02-03 at 16:13

Yes, it is refreshable. you can even export the connection file (under connection properties, definition, Export Connection File) and use it for other Excel Pivots.
PS: it has been a long time since you asked and Pavel published, but it is still the best information regarding about pivots and stored procedures imho. Thanks to the author 😊

Reply