Programming Excel with VBA and .NET

Avoid lists of lists

Excel can import XML that contains lists of lists, but it can’t export it. In XML schema terminology, a list is an element with a maxOccurs attribute greater than one. Therefore, XML using the following schema can’t be exported from an XML map (significant attributes are in bold):

<xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true" name="Order"
form="qualified">
    <xsd:complexType>
        <xsd:sequence minOccurs="0"> ... </xsd:sequence>
        <xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true"
        name="Line" form="qualified">
            <xsd:complexType>
                <xsd:sequence minOccurs="0"> ...</xsd:sequence>
            </xsd:complexType>
         </xsd:element>
    </xsd:complexType>
</xsd:element>

You can solve this problem by breaking the source XML into smaller pieces. In the case of SimpleOrder.xml, this means creating a separate file for each order node. The XML map’s root node then becomes Order, as shown in Figure 15-14.

Figure 15-14. Break XML into smaller files to avoid lists of lists

You can organize the new, smaller files into a separate folder or by using a unique file extension, such as ".ord". For example, the following code allows the user to select an order file to open in Excel:

Sub cmdOpenOrder( ) ' Get a filename to open. Use ".ord" extension for orders. Dim fname As String fname = Application.GetOpenFilename("Orders (*.ord),*.ord", 1, "Open an Order", _ "Open", False) If fname <> ...