Saving XML Data

Saving your data back to the existing XML file or a new file is as simple as a call to the Workbook object's SaveAsXMLData method. The SaveAsXMLData method takes two arguments, the file name to save to and the XML map object to retrieve the file schema from. Figure 3-14 shows the SaveAsXMLData method displayed in the Visual Basic Immediate window with its arguments shown via IntelliSense.


SaveAsXMLData[R7eft3ffje^s String. Map A s Xrn IM a p |

Figure 3-14. SaveAsXMLData method

Add a new procedure to the standard module you've been working with, and name it SaveXML.

Sub SaveXML()

Dim ExportMap As XmlMap

Set ExportMap = ActiveWorkbook.XmlMaps("cds_Map")

If ExportMap.IsExportable Then ActiveWorkbook.SaveAsXMLData

"C:\projects\Excel\cds_XML_out.xml", ExportMap


MsgBox ExportMap.Name & " cannot be used to export XML" End If End Sub

Before persisting your data, it's a good idea to ensure that the XML is exportable. The XMLMaps collection contains a read-only IsExportable method that returns True if any lists that refer to the map are exportable. A map is not exportable if an invalid value for an element is present or if required fields are not supplied.

Run the SaveXML macro from the Macros dialog box, accessible from the Developer ribbon. Figure 3-15 shows the XML generated by the SaveXML subroutine.

<?xml version=n1.0" encoding=nUTF-8n standalone=nyesn ?>

<cds xmlns:xsi="">

<artist>Nirvana</artist> <title >Nevermind </title > <releasedate>1991</releasedate> </cd>

<artist>Nirvana</artist> <title>In Utero</title> <releasedate>1993</releasedate> </cd>

<artist>Alice In Chains</artist> <title >Facelift</title > <releasedate>1990</releasedate> </cd> + <cd> + <cd> + <cd> + <cd> + <cd> + <cd> + <cd> + <cd>

<artist>Dream Theater</artist> <title>Images and Words</title> <releasedate>1992</releasedate> </cd> </cds>

Figure 3-15. Exported XML file

When saving an XML map to a file, Excel adds the processing instructions. Our original file was very basic and did not include them (of course, there's no harm in including them).

+1 0

Post a comment