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

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

        1. Hi aboladebabawale.

          I created a new SQL SP with 12 parameters to experiment a similar situation and it worked perfectly.
          Make sure you don’t have any other VBA codes that affects this one.
          Also, make sure the values you’re trying to pass to the SP are all legitimate values.
          The other possibility is that the Worksheet might be protected, so make sure it is not the case.

          Cheers.

  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?

    1. Hi Richard.
      I tried to find a way for get it work with Excel 2010.
      Unfortunately I’m unable to express a stable solution for that.
      However, I explain what you can do in few steps. I believe, it is not that practical, but, it’s better than nothing!
      You can follow the below steps:
      1- Instead of importing the results of the SP directly into the PowerPivot, import it into an excel worksheet.
      2- From PowerPivot tab select “Create Linked Table” then select the table imported from SQL server. Tick “My Table Has Headers”.
      3- Now press Alt+F11 and put the VBA code in a desired procedure, say “SelectionChange”
      4- Add a pivot table in a new worksheet and link it to the PowerPivot linked table then add the Values and Row as explained in the main article
      5- If you change the values of A2 OR B2 cells it refreshes the worksheet’s data
      6- Go to the PowerPivot tab again (from the Excel ribbon) and click on “Update All” button. This refreshes data of the linked table in PowerPivot.
      7- Now untick a selected Row and tick it again, it refreshes the results for the new values of A2 and B2 cells

      As I said, I don’t believe this would be a practical way to do the job.
      You can consider it as a simple work around.

      Cheers

  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.

    1. Hi Ananth.

      The general rule for SQL Server stored procedures using an XML input is that you need to define an XML type parameter in your stored procedure.
      From the above sample it seems CountryName is a string.

      Cheers

  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.

    1. Hi Ananth.

      I had a look at your codes. First of all, your SP retrieves nothing.
      When you’re executing a SP via Excel it supposed to show something to the user.
      So I modified your SP as follows:

      ALTER PROCEDURE [dbo].[testxmlpara] ( @parameters XML )
      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))
      SELECT *
      FROM @CountryName
      EXEC sp_xml_removedocument @idoc
      SET NOCOUNT OFF
      END

      Now the SP will show you the country names if you pass a valid XML to the SP.
      So you’ll need to put a valid XML in the “B2” cell in Excel and you should be good.
      Put the XML code you see in the screenshot below in “B2” and you should see the results in Excel:

      Now change the “US” to “AU” and you should see something like this:

      Cheers

  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.

    1. Hi SSAS1949.

      Thanks for your comment.
      We all know that we can use T-SQL queries to get data from a SQL Server database and usually we get data out of SQL Server tables or views.
      But in some cases you have to hire SQL Server stored procedures to follow the IT department security rules and hence make DBAs feel better.
      Or in some other cases you might need to use stored procedures just for performance optimisations.
      By the way, the main reason of publishing such articles is mainly knowledge sharing and nothing more.

      Cheers.

  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

    1. Hi Ananth.

      Are there any other differences between the two?
      I mean, are there any other differences between the two?

      – Both Excels are Excel 2013?
      – Did you enable the PowerPivot add-in on the 32 bit as well?
      – Are you pointing to the same SQL server and the same stored procedure?
      – Are you using the same VBA scripts in the 32 bit one as the 64 bit one?

      Generally speaking, if you have an Excel file created in Excel 2013, you should be able to used it in both 32 bit and 64 bit Excel 2013 anywhere else.

      To make sure that the 32 bit is not an issue, you can open the working Excel 2013 64 bit file in a machine using Excel 2013 32 bit.
      Just make sure that everything else is identical in moth machines.

      Cheers

  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

    1. Hi Ananth.

      I opened the Excel file on a x86 machine with Excel 32 bit installed and it worked perfectly.
      I suspect you have modified something like PowerPivot connection that leads the Excel connection to get read only, therefore you’ll get error 1004.

Leave a Reply