title: How to convert XML node contents to string in VBA?
body: <p>I'm trying to populate a list box with all of the XML nodes in an XML file in one column of the listbox, and then have the value of the corresponding node in the second column of the listbox. </p>
  
  <p>So, for example:</p>
  
  <p>Column one | Column Two</p>
  
  <p>firstname |   John</p>
  
  <p>surname | Smith      </p>
  
  <p>(dreadful formatting, but just wanted to give you a rough idea)</p>
  
  <p>I've accomplished the first part, and have listed all of the XML node names in one column, however I'm having difficulty with the latter part. </p>
  
  <p>Here's the code I have so far:</p>
  
  <pre><code>Public Sub LoadDocument()
  Dim xDoc As DOMDocument
  Set xDoc = New DOMDocument
  xDoc.validateOnParse = False
  If xDoc.Load(filepath) Then
  
     DisplayNode xDoc.ChildNodes
  Else
     ' The document failed to load.
     ' See the previous listing for error information.
  End If
  End Sub
  
  Public Sub DisplayNode(ByRef Nodes As IXMLDOMNodeList)
  
     Dim xNode As IXMLDOMNode
  
     For Each xNode In Nodes
      If xNode.nodeName = "#text" Then
  
      Else
      With xnodeListBox
      .AddItem (xNode.nodeName)
      .list(.ListCount - 1, 1) = (xNode.nodevalue)
      End With
        If xNode.HasChildNodes Then
           DisplayNode xNode.ChildNodes
        End If
      End If
     Next xNode
  
  End Sub
  </code></pre>
  
  <p>This line is the problem point:</p>
  
  <pre><code>.list(.ListCount - 1, 1) = (xNode.nodevalue)
  </code></pre>
  
  <p>Can anyone give me hand with this?</p>
  
  <p>EDIT:</p>
  
  <p>I believe I figured it out. </p>
  
  <p>What I <em>think</em> happened is this: The first node would contain the entire contents of the document, so it would have an absurdly large amount of characters. This meant that the contents of the first node couldn't be stored, and with the previous code, wouldn't move onto the other nodes. This may just be a limitation specific to the XML files i'm dealing with. </p>
  
  <p>To prevent that happening, I changed the code to this:</p>
  
  <pre><code>   Public Sub DisplayNode(ByRef Nodes As IXMLDOMNodeList)
  
     Dim xNode As IXMLDOMNode
  
     For Each xNode In Nodes
      If xNode.nodeName = "#text" Then
  
      Else
      With xnodeListBox
      .AddItem xNode.nodeName
      If (Len(xNode.nodeTypedValue) &lt; 300) Then
      .list(row, 1) = xNode.nodeTypedValue
      End If
      row = row + 1
      End With
      End If
  
        If xNode.HasChildNodes Then
           DisplayNode xNode.ChildNodes
        End If
     Next xNode
  
  End Sub
  </code></pre>