Dynamically Passing Parameters to a SQL Stored Procedure from PowerPivot Using VBA

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”

· 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”.

Published by Soheil Bakhshi

View all posts by Soheil Bakhshi

15 thoughts on “Dynamically Passing Parameters to a SQL Stored Procedure from PowerPivot Using VBA”

aboladebabawale says:

January 9, 2015 at 3:58 pm

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?

Reply
1. Soheil Bakhshi says:
  
  January 12, 2015 at 7:40 am
  
  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.
  
  Reply
  1. aboladebabawale says:
    
    January 20, 2015 at 11:22 am
    
    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.
    
    Reply
Richard Ray says:

February 11, 2015 at 12:07 pm

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?

Reply
Ananth says:

February 11, 2015 at 9:03 pm

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.

Reply
Ananth says:

February 12, 2015 at 8:16 pm

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.

Reply
nate says:

February 21, 2015 at 6:16 am

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.

Reply
Ananth says:

March 20, 2015 at 1:59 am

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

Reply
Ananth says:

March 20, 2015 at 6:39 pm

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

Reply
Ananth says:

March 23, 2015 at 10:46 pm

Yes , I did Soheil, but it is showing me the same error.

Regards,
Ananth.

Reply
novita hosama says:

November 30, 2015 at 2:47 pm

Hi Soheil Bakhshi ,

I want to rename the sheet in model. Is there anyway to rename it?

Thankyou

Reply
Lukas says:

March 13, 2019 at 9:47 pm

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?

Reply
Simak Tekno says:

February 25, 2020 at 7:30 am

Would love to see the open-source here!

Reply
Jeff says:

September 1, 2022 at 8:51 am

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

Reply
1. Soheil Bakhshi says:
  
  September 22, 2022 at 7:34 pm
  
  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
  
  Reply