Replace September 2022:

I wrote a brand new weblog put up masking the identical situation in Excel 365.

On this put up, I specific a simple approach to refresh a PowerPivot mannequin dynamically primarily based on SQL Server Saved Procedures. Let’s begin with SQL Server Administration Studio (SSMS) 2012 and use Journey Works 2012 database. Run the next script to see the ends in SSMS:

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

First parameter: Begin Product ID

Second Parameter: Examine Date

Outcomes:

Now we wish to see the outcomes for the next script:

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

 Outcomes:

Now we wish to do the identical with PowerPivot.

·         Open Microsoft Excel, I’m utilizing Excel 2013, then go to PowerPivot tab within the ribbon and click on “Handle”

·         Click on “Get Exterior Knowledge”

·         Click on “From Database”

·         Click on from SQL Server

·         Kind a pleasant title for the connection, kind a server title and database title then click on Subsequent

·         Choose “Write the question that may specify the info to import” then click on Subsequent

·         Kind a pleasant title for the question and put the next script within the SQL Assertion textual content field then click on End

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

·         As you’ll be able to see 24 rows are transferred

·         Now you’ll be able to see the ends in PowerPivot

·         Shut PowerPivot and go the info tab within the Excel ribbon then click on “Connections” from connections part

·         Choose SP_Connection1 then click on Properties

·         Go to “Definition” tab. As you’ll be able to see the connection is a read-only connection and we’re unable to alter the command textual content

·         The unhappy information is that the connection is read-only endlessly and we’re unable to change it from VBA.

·         Click on OK to shut the connection properties window. DO NOT shut the “Workbook Connections” window

·         Choose the SP_Connection1 once more and this time click on the “Add…” button and the press “Add to the Knowledge Mannequin”

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

·         This can add a brand new connection to the workbook with the identical settings

·         Choose the brand new connection that’s robotically named “SP_Connection11” then click on the “Properties” button to rename the connection to SP_Connection2

·         Change the connection title to SP_Connection2. Should you click on on the “Definition” tab then you’ll be able to see that the brand new connection is NOT read-only, so we’ll be capable to modify it utilizing VBA.

·          Click on OK to shut the connection properties. Now the connection is renamed to SP_Connection2.

·         Go to PowerPivot once more to examine what we have now within the mannequin now

·         As you see there’s a new desk added to the mannequin with the identical outcomes because the “PowerPivot_SP” desk

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 visible fundamental for Excel

·         From “Microsoft Excel Objects” double click on on “ThisWorkbook”

·         From the objects drop down choose “Workbook” and the choose “SheetChange” process

·         Copy and paste the next VBA scripts to alter the SP_Connection2 dynamically. The values of A2 and B2 cells will probably be handed to the SQL Server saved process and the connection will probably be refreshed to fetch the outcomes from SQL Server:

NOTE: The above code will refresh the PowerPivot knowledge everytime you modify the worth of the B2 cell within the present lively worksheet. You possibly can take away the MsgBox line from the code. I’ve put this half for testing functions.

·         Press “Alt+F11” once more to return to Excel and put 762 within the A2 cell and 2009-01-02 within the B2 cell and press Enter

·         As you’ll be able to see the message field exhibits us that the SP_Connection2 is modified primarily based on the values of cells A2 and B2.

·         Open PowerPivot once more to examine if the info are loaded to the mannequin appropriately

·         Lastly we must always save the file. Choose “Excel Macro-Enabled Workbook (*.xlsm)” from the save as kind, in any other case you’ll get the next message

Now you can delete the PowerPivot_SP desk from the PowerPivot mannequin.

Now you’ll be able to insert the pivot desk in excel, modify the values of A2 and B2 cells and the pivot desk will robotically refresh.

Right here is the outcomes of the saved process with totally different values for the parameters:

Begin Product ID = 727

Examine Date = 2009-01-02

Begin Product ID = 762

Examine Date = 2009-01-02

We’re completed now.

Possibly a few of you guys assume that it’s actually a ache that you just can not rename the desk and it’s columns from PowerPivot. Effectively, I ought to say that I do agree with you. However, sadly, it’s the way it works for now. I’ve completed plenty of investigations to discover a approach to modify the SQL Assertion in “Edit Desk Properties” from PowerPivot, however, it appears it’s untouchable trough VBA. Yow will discover the “Edit Desk Properties” from PowerPivot, Design tab then click on on “Desk Properties”.