6.4 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
figs/oxml_0640.gif


Figure 6-41. A backstage area storing information used by the VBA code
figs/oxml_0641.gif


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>       <quantity>4</quantity>    </item>    <item>       <sku>34548</sku>       <quantity>1</quantity>    </item> </sales>

Rather than expecting users of the spreadsheet to import or export this information themselves using the GUI, this spreadsheet provides buttons that import and export XML information. The first Import button on the left contains the code shown in Example 6-12.

Example 6-12. Importing from an XML file to an Excel XML map
Private Sub ImportFile_Click( )     Dim myMap As XmlMap     'reference map by name     Set myMap = ActiveWorkbook.XmlMaps("products_Map")          Dim source As String     source = Worksheets(2).Range("B1").Text          Dim append As Boolean          append = Range("D2").Value          myMap.AppendOnImport = append     myMap.AdjustColumnWidth = False               myMap.Import (source)          End Sub

First, this code retrieves the first XML map in the Excel spreadsheet from the workbook's XmlMaps collection. Next, it gets the source file from which it is to import from cell B1 of the worksheet shown in Figure 6-41. It collects the value of cell D2 on the main worksheet so it can tell Excel whether to append new data or replace the existing data in the map with the new data, by setting the AppendOnImport property of the map. To avoid columns changing sizes, the script explicitly sets AdjustColumnWidth to false. Finally, it calls the map object's Import method, giving it the source argument collected at the beginning. (That source can be a URL, not just a file reference.)

If the products.xml file listed in Example 6-10 is at the location specified by cell B1 of the Source sheet, you'll see a result like that of Figure 6-42.

Figure 6-42. Result of the first import from a file
figs/oxml_0642.gif


If you check the "Append on Import" checkbox, thereby changing cell D2's contents to true, and then click Import again, it will add the same three values to the map again, as shown in Figure 6-43.

Figure 6-43. Result of the second import from a file, with append
figs/oxml_0643.gif


Its companion Export button is simpler, containing the code shown in Example 6-13.

Example 6-13. Exporting from an XML file to an Excel XML map
Private Sub ExportFile_Click( )     Dim myMap As XmlMap     'reference map by number (6-12 referenced by name)     Set myMap = ActiveWorkbook.XmlMaps(1)          Dim dest As String     dest = Worksheets(2).Range("B2").Text          myMap.Export (dest)     End Sub

Like the Import version, it collects the first map in the workbook, and a location from the Sources worksheet. Instead of importing, though, it uses the Export method to drop the XML in the file specified. If the spreadsheet looks like Figure 6-43, clicking the right-hand Export button will produce the code shown in Example 6-14.

Example 6-14. Exporting from an Excel XML map to an XML file
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">     <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>     <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>

The map and buttons on the right-hand side behave differently. Rather than importing from and exporting to files, they import from and export to strings, using the ImportXML and ExportXML methods instead of Import and Export. You might want to do this if your data came from someplace other than a file, or if you need to do something to the XML before the import or export takes place. The string import method is shown in Example 6-15 and the string export method is shown in Example 6-16.

Example 6-15. Importing from an XML string to an Excel XML map
Private Sub ImportString_Click( )     Dim myMap As XmlMap     Set myMap = ActiveWorkbook.XmlMaps(2)          Dim sourceData As String         sourceData = Worksheets(2).Range("B4").Text          Dim append As Boolean          append = Range("D2").Value          myMap.AppendOnImport = append     myMap.AdjustColumnWidth = False              myMap.ImportXml (sourceData)      End Sub

Example 6-16. Exporting from an Excel XML map to an XML string
Private Sub ExportString_Click( )     Dim myMap As XmlMap     Set myMap = ActiveWorkbook.XmlMaps(2)          Dim result As String          myMap.ExportXml result               Worksheets(2).Range("B5").Value = result      End Sub

If the Sources sheet looks like Figure 6-41, clicking on the right-hand Import button of the Data sheet will produce the result shown in Figure 6-44.

Figure 6-44. Importing from a string on the Sources sheet
figs/oxml_0644.gif


If you now click on the right-hand Export button and then go look at the Sources sheet, you'll see the result shown in Figure 6-45.

Figure 6-45. The Sources sheet after an export
figs/oxml_0645.gif


While these examples are fairly simple, they've demonstrated several ways to get information into and out of Excel. You can extend these examples with more VBA to create applications that update their data automatically, issue queries against web sites and present results, or pass XML information to custom processes for further work.



Office 2003 XML
Office 2003 XML
ISBN: 0596005385
EAN: 2147483647
Year: 2003
Pages: 135

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net