CODEWITHSUNDEEP
excel-2010powerpivot
vinay singri
vinay singri

Reputation: 199

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.

Upvotes: 0

Views: 8331

Answers (3)

Bill Anton
Bill Anton

Reputation: 2970

Short Answer: No.

Long Answer: you could write some VBA code in the workbook that refreshes the data upon opening the file. Then you could create a stored procedure that regularly queries the dataset, and if a change is detected calls a script that opens the Excel file, triggering the VBA to refresh the Powerpivot, and then closes the Excel file.

Upvotes: 0

Mark
Mark

Reputation: 9428

All-new short answer: Yes :)

Take a look at the Excel Refresh Service on codeplex - it will refresh PowerPivot cubes and Excel data connections without requiring Excel plugins or macros

Upvotes: 1

ronak sanghavi
ronak sanghavi

Reputation: 26

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!

Upvotes: 1

Related Questions