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.
Continue reading “Dynamically Passing Parameters to a SQL Stored Procedure from PowerPivot Using VBA”