Programming Excel with VBA and .NET

Include all nodes if exporting

When you export XML, Excel takes the data found in mapped items on worksheets, applies the XML map, and generates XML nodes defined in the XML map’s schema. If some of the XML map’s data nodes are not mapped, that data is omitted from the exported XML.

In some cases, this is what you want. But if you are trying to read and write an XML file without losing content, you need to make sure that all elements from the XML map appear somewhere on the worksheet (even if they are hidden).

If a node contains a calculated value, you will need to perform the calculation in a nonmapped cell, then copy that value to the mapped cell before exporting. The Save Order button in Figure 15-17 copies the calculated subtotal, tax, and total values to cells created from the XML map before exporting the XML using the following code:

Sub cmdSaveOrder(  )
    ' Update mapped cells with calculated values.
    Range("XmlSubTotal") = Range("SubTotal")
    Range("XmlTax") = Range("Tax")
    Range("XmlTotal") = Range("Total")
    ' Create filename to save.
    Dim fname As String
    fname = ThisWorkbook.path & "\" & Range("OrderID") & ".ord"
    ' Save the order.
    ThisWorkbook.XmlMaps("Order_Map").Export fname, True
End Sub