Update September 2022:
I wrote a new blog post covering the same scenario in Excel 365.
In this post, I express an easy way to refresh a PowerPivot model dynamically based on SQL Server Stored Procedures. Let’s start with SQL Server Management Studio (SSMS) 2012 and use Adventure Works 2012 database. Run the following script to see the results in SSMS:
exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’
First parameter: Start Product ID
Second Parameter: Check Date
Results:
Now we want to see the results for the following script:
exec [dbo].[uspGetBillOfMaterials] 762, ‘2009-01-02’
Results:
Now we want to do the same with PowerPivot.
· Open Microsoft Excel, I’m using Excel 2013, then go to PowerPivot tab in the ribbon and click “Manage”
· Click “Get External Data”
· Click “From Database”
· Click from SQL Server
· Type a friendly name for the connection, type a server name and database name then click Next
· Select “Write the query that will specify the data to import” then click Next
· Type a friendly name for the query and put the following script in the SQL Statement text box then click Finish
exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’
· As you can see 24 rows are transferred
· Now you can see the results in PowerPivot
· Close PowerPivot and go the data tab in the Excel ribbon then click “Connections” from connections section
· Select SP_Connection1 then click Properties
· Go to “Definition” tab. As you can see the connection is a read-only connection and we are unable to change the command text
· The sad news is that the connection is read-only forever and we are unable to modify it from VBA.
· Click OK to close the connection properties window. DO NOT close the “Workbook Connections” window
· Select the SP_Connection1 again and this time click the “Add…” button and the click “Add to the Data Model”
![clip_image015[6]](https://i0.wp.com/www.biinsight.com/wp-content/uploads/2014/09/clip_image01561.jpg?ssl=1 "clip_image015[6]")
· Go to the “Tables” tab and select “PowerPivot_SP” then click “Open”
· This will add a new connection to the workbook with the same settings
· Select the new connection that is automatically named “SP_Connection11” then click the “Properties” button to rename the connection to SP_Connection2
· Change the connection name to SP_Connection2. If you click on the “Definition” tab then you can see that the new connection is NOT read-only, so we’ll be able to modify it using VBA.
· Click OK to close the connection properties. Now the connection is renamed to SP_Connection2.
· Go to PowerPivot again to check what we have in the model now
· As you see there is a new table added to the model with the same results as the “PowerPivot_SP” table
NOTE: DO NOT RENAME THE TABLE OR ANY OF THE COLUMNS. IF YOU DO SO, THE NEW CONNECTION WILL GET READ-ONLY AND YOU’LL BE UNABLE TO CHANGE THE SQL QUERY ANYMORE.
· Now press “Alt+F11” to open visual basic for Excel
· From “Microsoft Excel Objects” double click on “ThisWorkbook”
· From the objects drop down select “Workbook” and the select “SheetChange” procedure
· Copy and paste the following VBA scripts to change the SP_Connection2 dynamically. The values of A2 and B2 cells will be passed to the SQL Server stored procedure and the connection will be refreshed to fetch the results from SQL Server:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range(“B2”)) Is Nothing Then
‘Nothing
Else
ActiveWorkbook.Connections(“SP_Connection2”).OLEDBConnection.CommandText = “exec [dbo].[uspGetBillOfMaterials] ‘” & Range(“A2”).Value & “‘, ‘” & Range(“B2”).Value & “‘”
ActiveWorkbook.Connections(“SP_Connection2”).Refresh
MsgBox (“ConMod: ” & ActiveWorkbook.Connections(“SP_Connection2”).OLEDBConnection.CommandText)
End If
NOTE: The above code will refresh the PowerPivot data whenever you modify the value of the B2 cell in the current active worksheet. You can remove the MsgBox line from the code. I’ve put this part for testing purposes.
· Press “Alt+F11” again to go back to Excel and put 762 in the A2 cell and 2009-01-02 in the B2 cell and press Enter
· As you can see the message box shows us that the SP_Connection2 is changed based on the values of cells A2 and B2.
· Open PowerPivot again to check if the data are loaded to the model correctly
· Finally we should save the file. Select “Excel Macro-Enabled Workbook (*.xlsm)” from the save as type, otherwise you’ll get the following message
You can now delete the PowerPivot_SP table from the PowerPivot model.
Now you can insert the pivot table in excel, modify the values of A2 and B2 cells and the pivot table will automatically refresh.
Here is the results of the stored procedure with different values for the parameters:
Start Product ID = 727
Check Date = 2009-01-02
Start Product ID = 762
Check Date = 2009-01-02
We’re done now.
Maybe some of you guys think that it’s really a pain that you cannot rename the table and it’s columns from PowerPivot. Well, I should say that I do agree with you. But, sadly, it is how it works for now. I’ve done lots of investigations to find a way to modify the SQL Statement in “Edit Table Properties” from PowerPivot, but, it seems it is untouchable trough VBA. You can find the “Edit Table Properties” from PowerPivot, Design tab then click on “Table Properties”.
Discover more from BI Insight
Subscribe to get the latest posts sent to your email.
Hi,
I followed your steps as described here but I did not get my pivot table refreshed whenever I tried to change the values in A2 and B2 cells. I was wondering if there is something I was missing?
Hi aboladebabawale.
By following all the steps in sequence you should be able to get the PowerPivot refreshed.
As mentioned in the post, you shouldn’t rename the table or any columns.
Check if the connection got read-only.
Thanks for the comment.
Hi Soheil,
I was able to get the sample working but when I applied the concept to my need, I keep getting the error: “Run-time error ‘1004’: Application-defined or object-defined error”. And when i hit “Debug” it keeps highlighting the Refresh part of the code I posted below.
I was wondering if you could help me figure it out what this error might be about. I followed everything you said to letter and had expected to arrive at a working workbook…
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range(“L2”)) Is Nothing Then
‘Nothing
Else
ActiveWorkbook.Connections(“Conn_Str”).OLEDBConnection.CommandText = “EXEC [dbo].[vrc_ExportRISXLSFormat2] ‘” & Range(“A2”).Value & “‘, ‘” & Range(“B2”).Value & “‘, ‘” & Range(“C2”).Value & “‘, ‘” & Range(“D2”).Value & “‘, ‘” & Range(“E2”).Value & “‘, ‘” & Range(“F2”).Value & “‘, ‘” & Range(“G2”).Value & “‘, ‘” & Range(“H2”).Value & “‘, ‘” & Range(“I2”).Value & “‘, ‘” & Range(“J2”).Value & “‘, ‘” & Range(“K2”).Value & “‘, ‘” & Range(“L2”).Value & “‘”
ActiveWorkbook.Connections(“Conn_Str”).Refresh
MsgBox (“ConMod: ” & ActiveWorkbook.Connections(“Conn_Str”).OLEDBConnection.CommandText)
End If
End Sub
Thanks for your help.
I’m trying to do this with Excel 2010. Am I out of luck? When I create the connection in PowerPivot and return to Excel the only connection I have is a generic ‘PowerPivot Data’. Its description reads ‘This connection is used by Excel for communication between the workbook and embedded PowerPivot data, and should not be manually edited or deleted.’
Things aren’t in line with your post from there on out, so I may not be able to do it with this version?
Hi,
I followed your steps as described here and created a table “EmpDetails” with column names [CountryName], [id], [first name], [last name] and a Stored Procedure “GetEmpDetails” which is taking [CountryName] as the input parameter and returns all the details of the employee [CountryName], [id], [first name], [last name] as output. Finally I was successful in Dynamically Passing Parameters to a SQL Stored Procedure from PowerPivot Using VBA.
But when I try to do the above process using “XML” parameters like “”” I am getting error in this line “ActiveWorkbook.Connections(“Conn_Str”).Refresh”.
Initially I am entering normal text like “India” or “US” in EXCEL cell and VBA is reading this text but now I am passing the text in xml format.Is there any other way passing xml parameter to VBA.
Please help me as soon as possible.
Feel free to ask if you need any other information regarding my VBA code.
Hi Soheil Bakhshi ,
Thank You for your reply and I am posting the code that I have written
in sql. Using this Stored Procedure I was able to get data in Sql but when I try to update PowerPivot by entering parameter value in EXCEL cell I am getting error.
CREATE PROCEDURE [testxmlpara] (@parameters nvarchar(MAX))
AS
BEGIN
SET NOCOUNT ON
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @parameters
DECLARE @CountryName TABLE (country_name varchar(10))
INSERT INTO @CountryName (country_name)
SELECT country_name
FROM OPENXML (@idoc, ‘/MnOBI/Geo’,1)
WITH (country_name varchar(10))
EXEC sp_xml_removedocument @idoc
SET NOCOUNT OFF
End
GO
VBA code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range(“B2”)) Is Nothing Then
‘ Nothing
‘
Else
ActiveWorkbook.Connections(“SqlServer”).OLEDBConnection.CommandText = “EXECUTE [testxmlpara] ‘” & Range(“B2”).Value & “‘”
ActiveWorkbook.Connections(“SqlServer”).Refresh
End If
End Sub
I am getting error in “ActiveWorkbook.Connections(“SqlServer”).Refresh”
line.
Please forgive my ignorance; I don’t understand why someone would need to refresh the data model. My instinct is the outcome could be achieved with filters or slicers directly on the data model as it stands. Thanks for your help in understanding this.
Hi Soheil Bakhshi ,
Does it make any difference if I use Excel-32 bit instead of Excel-64 bit, because when I try to dynamically pass XML parameters to PowerPivot in Excel-64 bit it is working fine and data is getting refreshed, but when I do the same procedure using Excel- 32 bit, after changing parameter in Excel cell a window is popping up and showing me ”Run-time error ‘1004, Application-defined or object-defined error”, has occurred. If I am trying to debug the error, it is redirecting me to Vba code page and the page is highlighting the line following the line
( “Activeworbook.Connections(“Connection1″).Refresh”) .
Regads,
Ananth
Hi Soheil Bakhshi ,
Thanks for your reply
– Both Excels are Excel 2013, one is 32bit and another one is 64bit
– I enabled PowerPivot add-in on the 32bit
– I am using Sql server 2008 and Excel 2013 64 bit in one machine and Sql server 2012 and Excel 2013 32 bit in another machine
– Yes I am using same VBA scripts in both 32 bit one and 64 bit one
– One more point I forgot to mention in the last post, I was able to successfully refresh the power pivot data by passing the normal parameters instead of XML parameters in both 32bit and 64bit machines.
Regards,
Ananth
Yes , I did Soheil, but it is showing me the same error.
Regards,
Ananth.
Hi Soheil Bakhshi ,
I want to rename the sheet in model. Is there anyway to rename it?
Thankyou
Hi all. This is old post , but describes problem that is close to mine.
I’m using SQL SP to gather the data for the report.
It can be used in Excel as well as in other tools.
If I get data using standard “from other sources/SQL server it works fine
If I use PowerQuery or PowerPivot it doesn’t.
Problem is this SP doesn’t fabe defined fixed list of columns. It uses internally LOGID that is treated as column by PoewrQuery and PowerQuery shows error that this column is not available in the result set.
Ant it will never be.
SP has parameter “module” and this parameter tells SP what data to give. Set of fields can change dynamically.
The same problem I had wit Tableau, but not in Power BI.
Can it be somehow overtaken? Can I change something so PowerQuery could use this SP?
Would love to see the open-source here!
Hello,
Hope this thread is still being monitored.
I am trying to solve for the exact situation described in this solution however I am using Excel 2019 and it seems that the workbook connections workflow has changed. “Connections” is now “Queries & Connections” and when clicking through to the properties tab of an existing connection there is no longer a “Workbook Connections” window and no option in the workflow to “Add…” or “Add to Data Model”. I can simply only see the “Connection Properties” window with the “Some properties cannot be changed…” messaging.
Is there any workaround in 2019 to be able to edit the command text of an existing SQL connection?
Thanks
Hi Jeff,
Sure I do.
Look, your question motivated me to write a brand new blog post to cover your question. Thank you for asking such a good question on an old post.
Please read your answer here: https://www.biinsight.com/dynamically-passing-parameters-to-a-sql-stored-procedure-in-excel-365-using-power-query/
Hopefully that helps.
Cheers