link
In many workshops and sessions about PowerPivot the most frequently asked question has always been: how can I automate the PowerPivot Data Refresh in Excel? I talked about that a few weeks ago.
The sad answer is that this feature is not supported in PowerPivot v1. And from what I can see in CTP3, it will still be unsupported in PowerPivot v2, too. However, once you understand that what I’m going to cover is completely unsupported and might damage your PowerPivot workbook making it unreadable (so please make a copy of your original workbook before starting), I think it is a good idea to make a few consideration about that.
The first consideration is about what Excel can do. By using VBA or VSTO you can access to the Excel object model and you can force the Refresh of a PivotTable. When you refresh a PivotTable connected to a PowerPivot data model, you are simply instructing Excel to request data from an external engine. PowerPivot is considered an external engine, even if, from a technical point of view, it runs inside the same Windows process of Excel. The communication between Excel and the PowerPivot engine is made through an OLEDB connection, which is used to transfer XMLA commands by using the same syntax used to communicate with a regular Analysis Services instance. Thus, a first approach is to inject an XMLA command on that connection requesting the PowerPivot engine to refresh a table of your PowerPivot data model.
A second approach might be to reverse engineer a few .NET DLLs that have been written to implement the PowerPivot Excel AddIn. With some hacking you might access this API within your Excel workbook and then call some internal undocumented API.
Unfortunately, none of these approaches is really safe. The .NET DLLs reverse engineering is not easy, you have to interact with a large number of undocumented APIs and you might be in trouble looking for a safe way to call them (threading model is just one of the possible issues to consider). The XMLA approach is somewhat more documented. However, Microsoft explicitly says that this type of approach is not supported in PowerPivot for Excel (it will be different with Denali BISM Tabular models, but it’s out of scope in this discussion).The unsupported statement I mentioned has an important consequence: “This may appear to work but it is a dangerous solution and has the potential to break/damage your workbook unexpectedly. The recommendation is to stick with the PowerPivot refresh mechanisms despite the fact that they do not let you automate things.” Thus, it seems that doing these operations outside from the PowerPivot AddIn might break the Excel file, producing an unreadable workbook. If you think about that, losing the Excel file in this way could be very expensive, so plan a backup before starting and after every major release.
However, in case you are willing to continue after this warning, you should know that a 100% VBA solution is available on Tom Gleeson’s blog (link updated on 2016-02-26, thanks to Julie’s comment). I have to repeat this again: don’t call Microsoft support in case you break your Excel files by using this technique. They would bounce you (and blame me for the link in this post <grin/>).