O'Reilly logo

Office 2003 XML by Evan Lenz, Mary McRae, Simon St. Laurent

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

XML maps and user interface

Figure 6-40. XML maps and user interface

A backstage area storing information used by the VBA code

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> ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required