<p>I work in VBA, and want to parse a string eg</p>



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

<p>and get the X &amp; Y values into two separate integer variables.</p>

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

<p>How do I do this?</p>

[{'answer_id': 11325, 'author': 'rjzii', 'author_id': 1185, 'author_profile': 'https://Stackoverflow.com/users/1185', 'pm_score': 7, 'selected': True, 'text': '<p>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.</p>\n\n<p>You can find more on MSXML2.DOMDocument at the following sites:</p>\n\n<ul>\n<li><a href="https://web.archive.org/web/20161217090033/http://en.allexperts.com/q/XML-1469/Manipulating-XML-files-Excel.htm" rel="noreferrer">Manipulating XML files with Excel VBA &amp; Xpath</a></li>\n<li>MSXML - <a href="http://msdn.microsoft.com/en-us/library/ms763742(VS.85).aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms763742(VS.85).aspx</a></li>\n<li><a href="https://web.archive.org/web/20161030020427/http://www.xml.com:80/lpt/a/979" rel="noreferrer">An Overview of MSXML 4.0</a></li>\n</ul>\n'}, {'answer_id': 11406, 'author': 'Devdatta Tengshe', 'author_id': 895, 'author_profile': 'https://Stackoverflow.com/users/895', 'pm_score': 6, 'selected': False, 'text': '<p>Thanks for the pointers.</p>\n<p>I don\'t know, whether this is the best approach to the problem or not, but here is how I got it to work.\nI referenced  the Microsoft XML, v2.6 dll in my VBA, and then the following code snippet, gives me the required values</p>\n  \n<pre class="lang-vb prettyprint-override"><code>Dim objXML As MSXML2.DOMDocument\n\nSet objXML = New MSXML2.DOMDocument\n\nIf Not objXML.loadXML(strXML) Then  \'strXML is the string with XML\'\n    Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason\nEnd If\n \nDim point As IXMLDOMNode\nSet point = objXML.firstChild\n\nDebug.Print point.selectSingleNode(&quot;X&quot;).Text\nDebug.Print point.selectSingleNode(&quot;Y&quot;).Text\n</code></pre>\n'}, {'answer_id': 2796385, 'author': 'DK.', 'author_id': 336474, 'author_profile': 'https://Stackoverflow.com/users/336474', 'pm_score': 3, 'selected': False, 'text': '<p>This is an example OPML parser working with FeedDemon opml files:</p>\n\n  \n\n<pre class="lang-vb prettyprint-override"><code>Sub debugPrintOPML()\n\n\' http://msdn.microsoft.com/en-us/library/ms763720(v=VS.85).aspx\n\' http://msdn.microsoft.com/en-us/library/system.xml.xmlnode.selectnodes.aspx\n\' http://msdn.microsoft.com/en-us/library/ms256086(v=VS.85).aspx \' expressions\n\' References: Microsoft XML\n\nDim xmldoc As New DOMDocument60\nDim oNodeList As IXMLDOMSelection\nDim oNodeList2 As IXMLDOMSelection\nDim curNode As IXMLDOMNode\nDim n As Long, n2 As Long, x As Long\n\nDim strXPathQuery As String\nDim attrLength As Byte\nDim FilePath As String\n\nFilePath = "rss.opml"\n\nxmldoc.Load CurrentProject.Path &amp; "\\" &amp; FilePath\n\nstrXPathQuery = "opml/body/outline"\nSet oNodeList = xmldoc.selectNodes(strXPathQuery)\n\nFor n = 0 To (oNodeList.length - 1)\n    Set curNode = oNodeList.Item(n)\n    attrLength = curNode.Attributes.length\n    If attrLength &gt; 1 Then \' or 2 or 3\n        Call processNode(curNode)\n    Else\n        Call processNode(curNode)\n        strXPathQuery = "opml/body/outline[position() = " &amp; n + 1 &amp; "]/outline"\n        Set oNodeList2 = xmldoc.selectNodes(strXPathQuery)\n        For n2 = 0 To (oNodeList2.length - 1)\n            Set curNode = oNodeList2.Item(n2)\n            Call processNode(curNode)\n        Next\n    End If\n        Debug.Print "----------------------"\nNext\n\nSet xmldoc = Nothing\n\nEnd Sub\n\nSub processNode(curNode As IXMLDOMNode)\n\nDim sAttrName As String\nDim sAttrValue As String\nDim attrLength As Byte\nDim x As Long\n\nattrLength = curNode.Attributes.length\n\nFor x = 0 To (attrLength - 1)\n    sAttrName = curNode.Attributes.Item(x).nodeName\n    sAttrValue = curNode.Attributes.Item(x).nodeValue\n    Debug.Print sAttrName &amp; " = " &amp; sAttrValue\nNext\n    Debug.Print "-----------"\n\nEnd Sub\n</code></pre>\n\n<p>This one takes multilevel trees of folders (Awasu, NewzCrawler):</p>\n\n<pre class="lang-vb prettyprint-override"><code>...\nCall xmldocOpen4\nCall debugPrintOPML4(Null)\n...\n\nDim sText4 As String\n\nSub debugPrintOPML4(strXPathQuery As Variant)\n\nDim xmldoc4 As New DOMDocument60\n\'Dim xmldoc4 As New MSXML2.DOMDocument60 \' ?\nDim oNodeList As IXMLDOMSelection\nDim curNode As IXMLDOMNode\nDim n4 As Long\n\nIf IsNull(strXPathQuery) Then strXPathQuery = "opml/body/outline"\n\n\' http://msdn.microsoft.com/en-us/library/ms754585(v=VS.85).aspx\nxmldoc4.async = False\nxmldoc4.loadXML sText4\nIf (xmldoc4.parseError.errorCode &lt;&gt; 0) Then\n   Dim myErr\n   Set myErr = xmldoc4.parseError\n   MsgBox ("You have error " &amp; myErr.reason)\nElse\n\'   MsgBox xmldoc4.xml\nEnd If\n\nSet oNodeList = xmldoc4.selectNodes(strXPathQuery)\n\nFor n4 = 0 To (oNodeList.length - 1)\n    Set curNode = oNodeList.Item(n4)\n    Call processNode4(strXPathQuery, curNode, n4)\nNext\n\nSet xmldoc4 = Nothing\n\nEnd Sub\n\nSub processNode4(strXPathQuery As Variant, curNode As IXMLDOMNode, n4 As Long)\n\nDim sAttrName As String\nDim sAttrValue As String\nDim x As Long\n\nFor x = 0 To (curNode.Attributes.length - 1)\n    sAttrName = curNode.Attributes.Item(x).nodeName\n    sAttrValue = curNode.Attributes.Item(x).nodeValue\n    \'If sAttrName = "text"\n    Debug.Print strXPathQuery &amp; " :: " &amp; sAttrName &amp; " = " &amp; sAttrValue\n    \'End If\nNext\n    Debug.Print ""\n\nIf curNode.childNodes.length &gt; 0 Then\n    Call debugPrintOPML4(strXPathQuery &amp; "[position() = " &amp; n4 + 1 &amp; "]/" &amp; curNode.nodeName)\nEnd If\n\nEnd Sub\n\nSub xmldocOpen4()\n\nDim oFSO As New FileSystemObject \' Microsoft Scripting Runtime Reference\nDim oFS\nDim FilePath As String\n\nFilePath = "rss_awasu.opml"\nSet oFS = oFSO.OpenTextFile(CurrentProject.Path &amp; "\\" &amp; FilePath)\nsText4 = oFS.ReadAll\noFS.Close\n\nEnd Sub\n</code></pre>\n\n<p>or better:</p>\n\n<pre class="lang-vb prettyprint-override"><code>Sub xmldocOpen4()\n\nDim FilePath As String\n\nFilePath = "rss.opml"\n\n\' function ConvertUTF8File(sUTF8File):\n\' http://www.vbmonster.com/Uwe/Forum.aspx/vb/24947/How-to-read-UTF-8-chars-using-VBA\n\' loading and conversion from Utf-8 to UTF\nsText8 = ConvertUTF8File(CurrentProject.Path &amp; "\\" &amp; FilePath)\n\nEnd Sub\n</code></pre>\n\n<p>but I don\'t understand, why xmldoc4 should be loaded each time.</p>\n'}, {'answer_id': 5747032, 'author': 'Tommie C.', 'author_id': 608991, 'author_profile': 'https://Stackoverflow.com/users/608991', 'pm_score': 2, 'selected': False, 'text': '<p><strong>Update</strong></p>\n<p>The procedure presented below gives an example of parsing XML with VBA using the XML DOM objects. Code is based on a <a href="https://msdn.microsoft.com/en-us/library/Aa468547.aspx" rel="nofollow noreferrer">beginners guide of the XML DOM</a>.</p>\n  \n<pre class="lang-vb prettyprint-override"><code>Public Sub LoadDocument()\n    Dim xDoc As MSXML.DOMDocument\n    Set xDoc = New MSXML.DOMDocument\n    xDoc.validateOnParse = False\n    If xDoc.Load(&quot;C:\\My Documents\\sample.xml&quot;) Then\n        \' The document loaded successfully.\n        \' Now do something intersting.\n        DisplayNode xDoc.childNodes, 0\n    Else\n        \' The document failed to load.\n        \' See the previous listing for error information.\n    End If\nEnd Sub\n\nPublic Sub DisplayNode(ByRef Nodes As MSXML.IXMLDOMNodeList, _\n   ByVal Indent As Integer)\n\n   Dim xNode As MSXML.IXMLDOMNode\n   Indent = Indent + 2\n\n   For Each xNode In Nodes\n      If xNode.nodeType = NODE_TEXT Then\n         Debug.Print Space$(Indent) &amp; xNode.parentNode.nodeName &amp; _\n            &quot;:&quot; &amp; xNode.nodeValue\n      End If\n\n      If xNode.hasChildNodes Then\n         DisplayNode xNode.childNodes, Indent\n      End If\n   Next xNode\nEnd Sub\n</code></pre>\n<blockquote>\n<p><strong>Nota Bene</strong> - This initial answer shows the simplest possible thing I could imagine (at the time I was working on a very specific issue) .\nNaturally using the XML facilities built into the VBA XML Dom would be\nmuch better. See the updates above.</p>\n</blockquote>\n<p><strong>Original Response</strong></p>\n<p>I know this is a very old post but I wanted to share my simple solution to this complicated question. Primarily I\'ve used basic string functions to access the xml data.</p>\n<p>This assumes you have some xml data (in the temp variable) that has been returned within a VBA function. Interestingly enough one can also see how I am linking to an xml web service to retrieve the value. The function shown in the image also takes a lookup value because this Excel VBA function can be accessed from within a cell using = FunctionName(value1, value2) to return values via the web service into a spreadsheet.</p>\n<p><img src="https://i.stack.imgur.com/taSSE.png" alt="sample function" /></p>\n<pre class="lang-vb prettyprint-override"><code>\nopenTag = ""\ncloseTag = "" \n<br>\' Locate the position of the enclosing tags\nstartPos = InStr(1, temp, openTag)\nendPos = InStr(1, temp, closeTag)\nstartTagPos = InStr(startPos, temp, ">") + 1\n\' Parse xml for returned value\nData = Mid(temp, startTagPos, endPos - startTagPos)\n</pre></code>\n'}, {'answer_id': 27704551, 'author': 'mvanle', 'author_id': 1213722, 'author_profile': 'https://Stackoverflow.com/users/1213722', 'pm_score': 4, 'selected': False, 'text': '<p>You can use a XPath Query: </p>\n\n  \n\n<pre class="lang-vb prettyprint-override"><code>Dim objDom As Object        \'// DOMDocument\nDim xmlStr As String, _\n    xPath As String\n\nxmlStr = _\n    "&lt;PointN xsi:type=\'typens:PointN\' " &amp; _\n    "xmlns:xsi=\'http://www.w3.org/2001/XMLSchema-instance\' " &amp; _\n    "xmlns:xs=\'http://www.w3.org/2001/XMLSchema\'&gt; " &amp; _\n    "    &lt;X&gt;24.365&lt;/X&gt; " &amp; _\n    "    &lt;Y&gt;78.63&lt;/Y&gt; " &amp; _\n    "&lt;/PointN&gt;"\n\nSet objDom = CreateObject("Msxml2.DOMDocument.3.0")     \'// Using MSXML 3.0\n\n\'/* Load XML */\nobjDom.LoadXML xmlStr\n\n\'/*\n\' * XPath Query\n\' */        \n\n\'/* Get X */\nxPath = "/PointN/X"\nDebug.Print objDom.SelectSingleNode(xPath).text\n\n\'/* Get Y */\nxPath = "/PointN/Y"\nDebug.Print objDom.SelectSingleNode(xPath).text\n</code></pre>\n'}, {'answer_id': 27908457, 'author': 'Bob Wheatley', 'author_id': 4446538, 'author_profile': 'https://Stackoverflow.com/users/4446538', 'pm_score': 2, 'selected': False, 'text': '<p>Here is a short sub to parse a MicroStation Triforma XML file that contains data for structural steel shapes.</p>\n\n  \n\n<pre class="lang-vb prettyprint-override"><code>\'location of triforma structural files\n\'c:\\programdata\\bentley\\workspace\\triforma\\tf_imperial\\data\\us.xml\n\nSub ReadTriformaImperialData()\nDim txtFileName As String\nDim txtFileLine As String\nDim txtFileNumber As Long\n\nDim Shape As String\nShape = "w12x40"\n\ntxtFileNumber = FreeFile\ntxtFileName = "c:\\programdata\\bentley\\workspace\\triforma\\tf_imperial\\data\\us.xml"\n\nOpen txtFileName For Input As #txtFileNumber\n\nDo While Not EOF(txtFileNumber)\nLine Input #txtFileNumber, txtFileLine\n    If InStr(1, UCase(txtFileLine), UCase(Shape)) Then\n        P1 = InStr(1, UCase(txtFileLine), "D=")\n        D = Val(Mid(txtFileLine, P1 + 3))\n\n        P2 = InStr(1, UCase(txtFileLine), "TW=")\n        TW = Val(Mid(txtFileLine, P2 + 4))\n\n        P3 = InStr(1, UCase(txtFileLine), "WIDTH=")\n        W = Val(Mid(txtFileLine, P3 + 7))\n\n        P4 = InStr(1, UCase(txtFileLine), "TF=")\n        TF = Val(Mid(txtFileLine, P4 + 4))\n\n        Close txtFileNumber\n        Exit Do\n    End If\nLoop\nEnd Sub\n</code></pre>\n\n<p>From here you can use the values to draw the shape in MicroStation 2d or do it in 3d and extrude it to a solid.</p>\n'}, {'answer_id': 33103998, 'author': 'No Name', 'author_id': 1188613, 'author_profile': 'https://Stackoverflow.com/users/1188613', 'pm_score': 4, 'selected': False, 'text': '<p>Add reference Project->References Microsoft XML, 6.0 and you can use example code:</p>\n\n  \n\n<pre class="lang-vb prettyprint-override"><code>    Dim xml As String\n\n    xml = "&lt;root&gt;&lt;person&gt;&lt;name&gt;Me &lt;/name&gt; &lt;/person&gt; &lt;person&gt; &lt;name&gt;No Name &lt;/name&gt;&lt;/person&gt;&lt;/root&gt; "\n    Dim oXml As MSXML2.DOMDocument60\n    Set oXml = New MSXML2.DOMDocument60\n    oXml.loadXML xml\n    Dim oSeqNodes, oSeqNode As IXMLDOMNode\n\n    Set oSeqNodes = oXml.selectNodes("//root/person")\n    If oSeqNodes.length = 0 Then\n       \'show some message\n    Else\n        For Each oSeqNode In oSeqNodes\n             Debug.Print oSeqNode.selectSingleNode("name").Text\n        Next\n    End If \n</code></pre>\n\n<p>be careful with xml  node //Root/Person  is not same with //root/person, also selectSingleNode("Name").text is not same with selectSingleNode("name").text</p>\n'}, {'answer_id': 40899195, 'author': 'TJ Wilkinson', 'author_id': 6813795, 'author_profile': 'https://Stackoverflow.com/users/6813795', 'pm_score': 0, 'selected': False, 'text': '<p>Often it is easier to parse without VBA, when you don\'t want to enable macros. This can be done with the replace function. Enter your start and end nodes into cells B1 and C1.</p>\n\n<pre><code>Cell A1: {your XML here}\nCell B1: &lt;X&gt;\nCell C1: &lt;/X&gt;\nCell D1: =REPLACE(A1,1,FIND(A2,A1)+LEN(A2)-1,"")\nCell E1: =REPLACE(A4,FIND(A3,A4),LEN(A4)-FIND(A3,A4)+1,"")\n</code></pre>\n\n<p>And the result line E1 will have your parsed value:</p>\n\n<pre><code>Cell A1: {your XML here}\nCell B1: &lt;X&gt;\nCell C1: &lt;/X&gt;\nCell D1: 24.365&lt;X&gt;&lt;Y&gt;78.68&lt;/Y&gt;&lt;/PointN&gt;\nCell E1: 24.365\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/11305', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/895/']