Excel 2007 XML Source Maps - Refreshing Schemas

I have a spreadsheet that is reporting on xml docs generated by an a开发者_运维百科pplication. 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?

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