Here's a question for you super-geeks out there. Why would Microsoft create its own object designed to traverse XML documents?
I don't normally get into the high-minded technical side of Excel and Access. But I do use XML quite a bit in my work with Xcelsius. Last year, I noticed that Microsoft introduced new objects with Office 2007: The CustomXMLPart object, and the CustomXMLNode object.
The CustomXMLPart object allows you to programmatically add and work with custom XML parts. The CustomXMLNode object is designed to provide functionality similar to that found in the IXMLDOMNode interface when working with custom XML parts. Together, the CustomXMLPart and CustomXMLNode objects enable you to traverse an XML document without relying on the DOM interface found in MSXML.
As far as I can tell, these objects are actually designed to work with the custom XML parts in an office document. But you can use these new objects as a way to read any XML document without the DOM interface. All you have to do is add the XML file as a custom XML part to the active document.
In this example, I first add a standard XML file to the XML parts collection of the active workbook. Then I traverse the XML document using the new CustomXMLNodes collection.
-
Sub Add_Traverse_CustomXMLPart()
-
-
Dim oCustomPart As CustomXMLPart
-
Dim oCustomNode As CustomXMLNode
-
Dim oCustomNodes As CustomXMLNodes
-
-
'Add a Custom XML Part from a file and then load
-
Set oCustomPart = ActiveWorkbook.CustomXMLParts.Add
-
oCustomPart.Load "C:\EmployeeSales.xml"
-
-
'Return all nodes for the employee who has invoice amount over 3000
-
Set oCustomNodes= oCustomPart.SelectNodes("//Employee[InvoiceAmount>3000]")
-
For Each oCustomNode In oCustomNodes
-
Debug.Print oCustomNode.Text
-
Next
-
-
'Delete the Custom XML Part
-
oCustomPart.Delete
-
-
End Sub
To test out this code, enter this XML into notepad and save as C:\ EmployeeSales.xml .
-
<!--l version="1.0-->
-
2312
-
Mike
-
Alexander
-
100
-
2300
-
24601
-
Ethan
-
Alexander
-
200
-
3211
-
So if you're automating XML documents (whether they are custom XML parts or not), it looks as though you can skip the DOM interface. The obvious benefit is that you don't have to point to an external reference library when coding XML processes using these new objects. The obvious drawback is that your code won't work with versions before Office 2007.
Any guesses as to why Microsoft would choose to create new objects specifically designed to work with XML?
RELATED STUFF
- Zoom into Spreadsheet on Double-Click
- Mocking the ‘Merge & Center’ Icon
- VBE Tips I Wish I Knew 5 Years Ago
- Exploding a Dataset using a PivotTable
- Highlighting the Active Row and Column
- ActiveX Controls and Intellisense
- Sending Data to Excel using Access SQL
- Transposing a Dataset in Access
- Using the Switch Function in Excel
- Why is Excel so Negative about Hiding Subtotals
I think it's part of their patent infringement strategy:
Microsoft Penalized $200M for Word XML Patent
http://www.tomshardware.com/news/microsoft-word-xml-patent-infringement,7861.html
Crikey Mike...with in-depth posts like these, you're constantly reminding me that you're smarter than I think you are!
(...which was plenty smart to start with)
I think that having nore than one choice is always a plus.
I target only 2007+ versions and not having to rely on an external library may be worthwile.
If we go deeper, though, I'm pretty sure we'll find a few subtle differences between the two.
No joke you know what you are talking about , thank you
hi, can you please adjust your xml so that it's readable? it seems that the xml tags are not coming through. thanks for the code!