Consulting

Results 1 to 4 of 4

Thread: PowerPivot VBA

  1. #1

    PowerPivot VBA

    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,

  2. #2
    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?.....

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,839
    Location
    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)?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    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/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •