How to parse XML using vba

Question

I work in VBA, and want to parse a string eg

<PointN xsi:type='typens:PointN'  xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'  xmlns:xs='http://www.w3.org/2001/XMLSchema'>     <X>24.365</X>     <Y>78.63</Y> </PointN> 

and get the X & Y values into two separate integer variables.

I'm a newbie when it comes to XML, since I'm stuck in VB6 and VBA, because of the field I work in.

How do I do this?

Answer 1

Thanks for the pointers.

I don't know, whether this is the best approach to the problem or not, but here is how I got it to work. I referenced the Microsoft XML, v2.6 dll in my VBA, and then the following code snippet, gives me the required values

Dim objXML As MSXML2.DOMDocument  Set objXML = New MSXML2.DOMDocument  If Not objXML.loadXML(strXML) Then  'strXML is the string with XML'     Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason End If   Dim point As IXMLDOMNode Set point = objXML.firstChild  Debug.Print point.selectSingleNode("X").Text Debug.Print point.selectSingleNode("Y").Text 

Answer 2

This is a bit of a complicated question, but it seems like the most direct route would be to load the XML document or XML string via MSXML2.DOMDocument which will then allow you to access the XML nodes.

You can find more on MSXML2.DOMDocument at the following sites:

• Manipulating XML files with Excel VBA & Xpath
• MSXML - http://msdn.microsoft.com/en-us/library/ms763742(VS.85).aspx
• An Overview of MSXML 4.0