Excel 2007 XML Source Maps - Refreshing Schemas

Question

I have a spreadsheet that is reporting on xml docs generated by an application. I'm using the functionality that allows you to create the format of the spreadsheet by dragging and dropping elements from the XML Source pane into your sheet.

I don't have a schema (at the moment) - Excel is just inferring from a supplied XML document.

Due to a spec change the XML format has changed - there are now some extra elements at a couple of places.

I can't see how to get Excel to refresh the fields available in the XML Source pane (ie in the inferred schema) based on an updated XML doc. It seems like I have to start from scratch with a new spreadsheet if the XML gets extra fields.

Is there a way around having to do this?

Answer 1

I was in a similar situation and in my case it was easier to just start from scratch, i.e. add every field manually. Before I did that I had a look at how Excel manages these connections and have put the relevant (and I find selfexplanitory) methods in the following example (the code assumes there is a worksheet called Tabelle1 with an excel-list and two xml-linked filed in cells A1 and B1 and further in the schema the XPath to /dataroot/Projekte/ID is valid -- that said, change accordingly to try it out).

So I assume with these methods/ functions you should be able to

• add your second (new) schema, then
• read out all the relevant information as shown below i.e. read out the XPath of the field on the old schema,
• delete the old XPath reference and
• add it again for that cell using the new schema.

BUT I haven't tried this myself (because as I said in my example it was just 9 fields/ cells and 2 tables)

Sub mainExcelSchemaExamples()

    ' View Inferred (or explicitly inserted) Schema
    Dim myxmlmap As XmlMap
    Dim myXMLSchemaString As String

    Set myxmlmap = ActiveWorkbook.XmlMaps(1)

    myXMLSchemaString = ActiveWorkbook.XmlMaps(1).Schemas(1).XML
    Debug.Print myXMLSchemaString

    ' Show XPath
    Dim mySheet As Worksheet
    Set mySheet = ActiveWorkbook.Sheets("Tabelle1")

    ' From a List
    Debug.Print mySheet.ListObjects(1).ListColumns(2).XPath.Map, _
            mySheet.ListObjects(1).ListColumns(2).XPath

    ' Single field
    Debug.Print mySheet.Range("A1").XPath.Map, mySheet.Range("A1").XPath
    Debug.Print mySheet.Range("B1").XPath.Map, mySheet.Range("B1").XPath

    ' Add XPath (and XML-Map)
    mySheet.Range("A2").XPath.SetValue Map:=myxmlmap, XPath:="/dataroot/Projekte/ID"

End Sub

I hope this helps as starting point.

Regards Andreas