VBA/Excel/Access/Word/XML/XML Data

Loading and Retrieving the Contents of an XML File

 
          
Sub ReadXMLDoc() 
    Dim xmldoc As MSXML2.DOMDocument50 
    Set xmldoc = New MSXML2.DOMDocument50 
    xmldoc.async = False 
    If xmldoc.Load("C:\yourFile.xml") Then 
        Debug.Print xmldoc.XML 
        Debug.Print xmldoc.Text 
    End If 
End Sub

Retrieving Information from Element Nodes

 
Sub IterateThruElements() 
    Dim xmldoc As MSXML2.DOMDocument50 
    Dim xmlNode As MSXML2.IXMLDOMNode 
    Dim xmlNodeList As MSXML2.IXMLDOMNodeList 
    Dim myNode As MSXML2.IXMLDOMNode 
    Set xmldoc = New MSXML2.DOMDocument50 
    xmldoc.async = False 
    xmldoc.Load ("C:\yourFile.xml") 
    Set xmlNodeList = xmldoc.getElementsByTagName("*") 
    For Each xmlNode In xmlNodeList 
        For Each myNode In xmlNode.childNodes 
          If myNode.nodeType = NODE_TEXT Then 
            Debug.Print xmlNode.nodeName & "=" & xmlNode.Text 
          End If 
        Next myNode 
    Next xmlNode 
    Set xmlDoc = Nothing 
End Sub

Retrieving Specific Information from Element Nodes

 
Sub SelectNodesByCriteria() 
    Dim xmldoc As MSXML2.DOMDocument50 
    Dim xmlNodeList As MSXML2.IXMLDOMNodeList 
    Dim myNode As MSXML2.IXMLDOMNode 
    Set xmldoc = New MSXML2.DOMDocument50 
    xmldoc.async = False 
    xmldoc.Load ("C:\yourFile.xml") 
    Set xmlNodeList = xmldoc.selectNodes("//Name") 
    If Not (xmlNodeList Is Nothing) Then 
        For Each myNode In xmlNodeList 
            Debug.Print myNode.Text 
            If myNode.Text = "old Text" Then 
                myNode.Text = "new Text" 
                xmldoc.Save "C:\newFile.xml" 
            End If 
        Next myNode 
    End If 
    Set xmlDoc = Nothing 
End Sub

Retrieving the First Matching Node

 
Sub SelectSingleNode() 
    Dim xmldoc As MSXML2.DOMDocument50 
    Dim xmlSingleNode As MSXML2.IXMLDOMNode 
    
    Set xmldoc = New MSXML2.DOMDocument50 
    xmldoc.async = False 
    xmldoc.Load ("C:\yourFile.xml") 
    Set xmlSingleNode = xmldoc.SelectSingleNode("//Name") 
    If xmlSingleNode Is Nothing Then 
        Debug.Print "No nodes selected." 
    Else 
        Debug.Print xmlSingleNode.Text 
    End If 
    Set xmlDoc = Nothing 
End Sub

Working with XML Document Nodes

 
Sub LearnAboutNodes() 
    Dim xmldoc As MSXML2.DOMDocument50 
    Dim xmlNode As MSXML2.IXMLDOMNode 
    Set xmldoc = New MSXML2.DOMDocument50 
    xmldoc.async = False 
    xmldoc.Load ("C:\yourFile.xml") 
    If xmldoc.hasChildNodes Then 
        Debug.Print "Number of child Nodes: " & xmldoc.childNodes.length 
        For Each xmlNode In xmldoc.childNodes 
            Debug.Print "Node name:" & xmlNode.nodeName 
            Debug.Print "Type:" & xmlNode.nodeTypeString & "(" & xmlNode.nodeType & ")" 
            Debug.Print "Text: " & xmlNode.Text 
        Next xmlNode 
    End If 
    Set xmlDoc = Nothing 
End Sub