Automatically refreshing a powerpivot-based model

[menegocci]

Hi,

I have a powerpivot-based model, and was looking through the web to automate the process of refreshing the graphs without having to open the powerpivot window, and then clicking on the "Refresh All" button (I'm using Excel 2010). I came across the VBA code written by Goban Saor on this blog: VBA Code to Automate a PowerPivot Refresh | Gobán Saor The problem is that I don't know enough about VBA to make the code provided on the website work with my spreadsheet, and I was wondering if anybody could help me. Which inputs in the code do I need to change to make it work with my specific file on Excel 2010 (if any)? The name of my Excel file is PTM.xlsm, and the name of the pivot table is Table1. Will the VBA code go on the Workbook script, or under the specific sheet? Since I have 5 sheets linked to the same underlying powerpivot, do I need to change anything in the code to account for that? Thank you for the help ahead of time!

 

[markmellow]

Hi,

I have a powerpivot-based model, and was looking through the web to automate the process of refreshing the graphs without having to open the powerpivot window, and then clicking on the "Refresh All" button (I'm using Excel 2010). I came across the VBA code written by Goban Saor on this blog: VBA Code to Automate a PowerPivot Refresh | Gobán Saor The problem is that I don't know enough about VBA to make the code provided on the website work with my spreadsheet, and I was wondering if anybody could help me. Which inputs in the code do I need to change to make it work with my specific file on Excel 2010 (if any)? The name of my Excel file is PTM.xlsm, and the name of the pivot table is Table1. Will the VBA code go on the Workbook script, or under the specific sheet? Since I have 5 sheets linked to the same underlying powerpivot, do I need to change anything in the code to account for that? Thank you for the help ahead of time!

Have you tried just recording your own macro? I saw the VBA code there in your link. The code is performing wayyyy more actions than you would want it to, potentially malicious since it goes to a website (....ww.w3.org/2001/XM...). and since you don't know much vba it is much better for practice that you attempt to make the code yourself rather than rely on a overly complex codes like that. This is just a quick warning before you put on that code.

 

[menegocci]

Hi marmellow,

It is not possible to simply record a macro to do that, since in Excel 2010, the PowerPivot tool is not accessible through simple VBA, hence why the code has to go through a couple of unusual routes to get it to work. From all of the responses on Saor's website, and other trustworthy sources, there is nothing malicious about the code, although there is a small chance that the code will render your spreadsheet unusable (a backup should solve this problem).
Thanks,