PowerPivot update on demand

[RichardRayJH]

I have a PowerPivot project that would be greatly improved if there were a way to update the PowerPivot data from the Excel workbook. Yes, I can train the users to use PowerPivot at least enough to do the update, but it's an extra step and takes them through a environment they otherwise don't need to see.

I've done a lot of googling and reading and haven't found a way to do this that seems to have been widely accepted. Is there a good way? Or is it just something that's best not to mess around with?

 

[trankillity]

You mean just by selecting any cell in a pivot table, going to Analyze on the PIVOTTABLE TOOLS tab and going Refresh All? I believe that triggers a refresh of the data on demand. I would imagine you could also quite easily make a VBA macro to do that and tie it to a button.

 

[RichardRayJH]

No, that's not the scenario. I have two cells on one tab of the workbook that get updated with start and end dates. When the workbook is refreshed those dates are used to recalculate the data in two info mart tables. Those info mart tables are the source for the three PowerPivot datasets. Once those are updated the pivot tables in the workbook are refreshed. I'd like to be able to trigger the PowerPivot data refresh after the info mart ables are updated, without trusting the users to remember to open the PowerPivot window and do the refresh.

Refreshing the PowerPivot data is the missing piece.

 

[scottsen]

2013? There is ActiveWorkbook.Model.Refresh ... or something like that... in vba. In 2010, you are totally hosed.

 

[RichardRayJH]

2013? There is ActiveWorkbook.Model.Refresh ... or something like that... in vba. In 2010, you are totally hosed.

Yep, 2010. Bummer. Thanks for the authoritative response, though.