PowerPivot VBA

[Marcster]

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]

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]

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]

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...-excel-2013-2/