A few days ago a post about VSTO-based PowerPivot Workbook in the PowerPivot Team Blog shown a workaround for the lack of PowerPivot API in Excel. In reality, I don’t like this workaround at all, because it relies on displaying message boxes to the end user in order to click “Update” button in the PowerPivot Ribbon. I understand the reasons why Microsoft probably is not publishing such API: doing that, they should support it for many years and probably they want the freedom to change it in the upcoming releases. I don’t know whether they already did it between v1 (2008 R2) and v2 (Denali), but my speculation is that a future version of Excel might integrate PowerPivot in a stronger way and the logical consequence is that its API would become part of the Excel object model. As I said, this is only my hypothesis and I don’t have any data to support it.
That said, the problem is that the lack of a PowerPivot API is really a missed opportunity today. There are companies, like Predixion, that take the risk of accessing to an undocumented and unsupported API to run their solutions. They generate PowerPivot workbooks from scratch with their Add-In, using PowerPivot compression to ease the data transfer from customer to their cloud infrastructure. They know they might have to rewrite part of their add-in for every new version of PowerPivot, and in case of any issue, they cannot rely on Microsoft support. It is a very expensive choice that cannot be justified in a large number of scenarios.
Between these two extremes, I would like a simple workaround to simply automate the PowerPivot Refresh operation from VBA/VSTO. Even in an unsupported way. Do you have the same hope?