PDA

View Full Version : [SOLVED] PowerPivot VBA



Marcster
01-22-2014, 11:40 AM
Hi People,

Using Excel 2013 with PowerPivot.
In the PowerPivot window > Design Tab > Table Properties I see where I originally inserted SQL code.

Is it possible to change this SQL code by using VBA?.

Thanks,

Marcster
01-22-2014, 12:15 PM
Or how to change the SQL to accept parameters. I am trying to set it up so instead of hard coding the date in the SQL I want to be able to say, input a date in an Excel sheet cell and the SQL will look up that cell for the date required. Can this be done with VBA?. Or is there a way without VBA?.....

Bob Phillips
01-27-2014, 02:43 AM
I am pretty sure that you cannot change the SQL from VBA in 2010, and even though the OM is more exposed in 2013 I think that is still out of scope (I can't check unfortunately as my PP isn't working in 2013 at the present).

I did think of using stored procedures, but PP won't dynamically prompt for the parameters in the SQL, you have to define them in the SQL view. A bit of a restriction.

How about creating SPs that take a date parameter but if no parameter is passed it bvrings it all back, then use the pivot to pick out what you want (dare I say slicer)?

Marcster
01-29-2014, 05:12 AM
Hi xld,

Yep, updating the sql in PP via vba isn't possible, so using slicers on all the data until I find a more efficient way.

Came across this article though: http://dmoffat.wordpress.com/2013/08/14/building-a-flexible-and-efficient-client-side-powerpivot-solution-in-excel-2013-2/