As regards the original code, the answer is no; linked tables cannot be refreshed via the XMLA method I use. You could of course, simply automate the export of the Excel tables to, say, a CSV format and then refresh from the disk. Biggest problem with that approach is PowerPivot’s insistence on using absolute file addresses, which makes sharing the resulting workbook more cumbersome (requires that the saving folder on each machine be the same). Another solution is to use some variation on SendKeys to automate the “button push”, can be done, but pretty it’s not.
So what’s an automate’r to do? Use PowerPivot’s oData import facility, that’s what.
In a previous example I had demonstrated using oData to provide a feed from a master-workbook to client work-books, so I already had code in Python to generate a simple oData feed (it’s essentially an ATOM feed; yeah, the same format used by many blog engines (usually alongside RSS2.0), and offered as a output format by many web services, Twitter and Google Docs, for example).
In PowerPivot itself, the range tables are fetched using a “127.0.0.1:8081/range/” endpoint.
So, for example, to fetch a table from a range whose top-left-hand-corner is Sheet2!A1, use the URL “127.0.0.1:8081/range/Sheet2!A1”.
For an Excel 2007/2010 table named InvoiceHeaders use “127.0.0.1:8081/range/InvoiceHeaders[%35All]” (the %35 is URL encoded “#”).
While the workbook is in “manual mode”, the in-process server will fetch on-demand any valid tabular range (but be careful not to “lock” the workbook, by, for example, editing a cell, while this is happening).
When in “automated refresh mode”, the VBA code will lock out any on-demand refreshes (it’s best in any case, to close PowerPivot’s window before doing any XMLA refreshes); then use the list of ranges provided to fetch the tables required, and initiate an auto-refresh.
Everything should go smoothly as long as the list matches exactly (case sensitive) the URLs assigned to tables with PowerPivot.
To try this out, download the latest version of HAMMER, go to the distribution folder, open the InProcess_oData workbook.
In the Control sheet, click the Enable HAMMER button, followed by the Start oData Server button. If you think port 8081 is likely to be already in use on your PC, go to the PythonCode sheet and change to a free port.
Then go to the Data sheet, change the tables, and refresh PowerPivot using the “arrow” button provided.
Make sure to click the Stop Server button, before exiting Excel, otherwise the port-listening thread will keep Excel alive in the background.
Of course, auto refreshing same-workbook “linked” tables is but one use this could be put to. Other possible uses would be taking advantage of Excel powers that the PowerPivot import facility still lack, such as XML Maps. Or, using 3rd party libraries or bespoke code to access “non-standard” (i.e. the majority that don’t use ATOM!) web services or data sources (such as XBRL).