Office 2003 XML

Loading and Saving XML Documents from VBA

While the GUI provides a convenient way to work with whatever XML you encounter, you may want to create applications that work with XML on a regular basis, and don’t want the user of the spreadsheet to have to interact with XML directly. Using Visual Basic for Applications, you can create spreadsheets that load XML and save XML through Excel’s maps without the user even needing to know where their data is coming from. The spreadsheet shown in Figures 6-40 and 6-41 will be used to demonstrate how this works.

Figure 6-40. XML maps and user interface

Figure 6-41. A backstage area storing information used by the VBA code

The worksheet shown in Figure 6-40 contains four buttons, a checkbox linked to cell D2, and two XML maps. The left-hand map expects data like that shown in Example 6-10, while the right-hand map expects data like that shown in cell B4 of Figure 6-41 or like that shown in Example 6-11.

Example 6-10. Simple product information format

<products>
   <item>
      <sku>34542</sku>
      <price>29.42</price>
   </item>
   <item>
      <sku>34546</sku>
      <price>19.24</price>
   </item>
   <item>
      <sku>34548</sku>
      <price>99.42</price>
   </item>
</products>

Example 6-11. Simple sales information format

<sales> <item> <sku>34542</sku> <quantity>10</quantity> </item> <item> <sku>34546</sku> ...