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

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:

powerpivot01

Now we want to see the results for the following script:

exec [dbo].[uspGetBillOfMaterials] 762, ‘2009-01-02’

 

Results:

powerpivot02

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

powerpivot03

·         Type a friendly name for the connection, type a server name and database name then click Next

powerpivot04

·         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’

 

powerpivot05

·         As you can see 24 rows are transferred

powerpivot06

·         Now you can see the results in PowerPivot

powerpivot07

·         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

powerpivot08

·         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]

·         Go to the “Tables” tab and select “PowerPivot_SP” then click “Open”

powerpivot09

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

powerpivot10

·          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

powerpivot11

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

powerpivot12

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.

powerpivot13

·         Open PowerPivot again to check if the data are loaded to the model correctly

powerpivot14

·         Finally we should save the file. Select “Excel Macro-Enabled Workbook (*.xlsm)” from the save as type, otherwise you’ll get the following message

powerpivot15

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

powerpivot16

Start Product ID = 762

Check Date = 2009-01-02

powerpivot17

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

powerpivot18

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

  1. 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?

    1. 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.

      1. 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.

  2. 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?

  3. 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.

  4. 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.

  5. 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.

  6. 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

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

  8. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.