Refresh Pivot Table With XML From ServerXMLHTTP Response

Question

I have succesfully made an Ajax like request to a web service using Microsoft Excel.

My goal is to have a button that the user can click to pull down the information and refresh the pivot tables and charts in the excel document to reflect the udpated information.

Can I set the Pivot Table to have an XML as it's source? If not, what about a table?

Otherwise, I guess I would need to have the macro clear existing information, repopulate and then refresh the pivot tables?

Can the Pivot table that only exists within the Macro? I'm currently getting the information with in a "Sub" function.

I currently have the information loaded as a DomDocument.

    Dim xmlResult As MSXML2.DOMDocument
    Set xmlResult = New MSXML2.DOMDocument
    xmlResult.LoadXML (results)

Answer 1

There are two ways to do it.

The first way is using XML maps. First, using option XML Source from menu Developer (in Excel 2003, I think it was under menu Data), you create a map from an XML schema or a sample XML data file into a worksheet. Once that's done you may import XML data, which will show in the mapped range. You should then assign the mapped range a name and finally build a pivot table on the named range. Refreshing of the mapped XML ranges from a HTTP service is relatively easily scriptable in VBA. Your named range will automatically adjust to the new size of data. The obvious limitation here is the size of worksheet to hold your data, which was often an issue before Excel 2007 with only 65,000 lines.

Another way I only vaguely recall and it involved setting the Recordset property of PivotCache object. Recordset would normally come from a database query but it can be build out of an XML, provided that it is a MS Persist XML. That would make it somewhat more difficult because you would have to transform your XML into MS Persist XML. Here's a script which converts Recordset to MS Persist XML and back: http://www.freevbcode.com/ShowCode.Asp?ID=1204