Search code examples
excel-2010powerpivot

Refreshing powerpivot data automatically

I wanted to know if there are any ways in which we can create an option of scheduling an automatic refresh for the power pivot data when ever there is change of data at the server side without using any share point farm.

Solution

  • This has worked for me.

    My situation was: I wanted to programatically set value of a Pivottable Report Filter, which indirectly supplies data to the Powerpivot table. After data in Powerpivot table is refreshed I wanted to refresh the Powerpivot tables built on the Powerpivot table. This is what I wrote:

    ActiveSheet.PivotTables("name").PivotFields( _
    "Participant Id").ClearAllFilters
ActiveSheet.PivotTables("name).PivotFields( _
    "Participant Id").CurrentPage = "Raj"
     AppActivate "PowerPivot For Excel - Assessment Tool 2.xlsm"

    Sheets("sheet1").PivotTables("PivotTable9").RefreshTable

    The trick is to keep the Powerpivot window open before executing the above code. Just click on the "Powerpivot Window Launch" button from the PP add-in in Excel.

    Hope this helps you!