Programming Excel with VBA and .NET

Name

workbook.XmlImportXml(Data, ImportMap, [Overwrite], [Destination])

Synopsis

Imports XML data into a list in the workbook. Returns an xlXMLImportResult value indicating whether the import succeeded.

This method is identical to XmlImport, however, it accepts a string argument containing the XML data, rather than the address of an XML file. This allows you to use XML data returned from web services and to perform XSL transformations before displaying XML data in Excel.

The following code performs an XSL transformation to limit the data displayed when importing XML data to a new worksheet:

' Requires reference to Microsoft XML Dim ws As Worksheet, xmap As XmlMap Dim msg As String, xml As String Dim xdoc As New DOMDocument, xstyle As New DOMDocument ' Create a new worksheet for the data. Set ws = ThisWorkbook.Worksheets.Add ' Load XML. If Not xdoc.Load("http://www.mstrainingkits.com/excel/ExcelObjects.xml") Then _ MsgBox "Error loading XML source." ' Load XSL transform. If Not xstyle.Load("http://www.mstrainingkits.com/excel/ObjByDate.xslt") Then _ MsgBox "Error loading XSL transform." ' Transform XML. xml = xdoc.transformNode(xstyle) ' Display results. ret = ThisWorkbook.XmlImportXml(xml, xmap, , ws.Range("A1")) Select Case ret Case XlXmlImportResult.xlXmlImportElementsTruncated msg = "Data was truncated." Case XlXmlImportResult.xlXmlImportSuccess msg = "XML data imported successfully." Case XlXmlImportResult.xlXmlImportValidationFailed msg = "XML was not valid." End Select MsgBox ...