CODEWITHSUNDEEP
excelvbaxml-parsingtreeview
Nick
Nick

Reputation: 533

VBA Reading XML into Treeview in Excel

I have a small form with a Treeview control in Excel. References include OLE Automation, Microsoft Forms 2.0, Microsoft Windows Common Controls 6.0 (SP6), and Microsoft XML, v6.0 I call the LoadTreeViewFromXmlFile giving a valid path and filename on UserForm_Initialize() like:


    Call LoadTreeViewFromXmlFile("C:\Users\...\my.xml", TreeView1)

but I get an Error 13 Type mismatch, in Sub AddChildrenToTreeView and more specifically at the For Each loop. Can't understand why. Could someone advise me please? Thank you very much in advance..

Private Sub LoadTreeViewFromXmlFile(ByVal file_name As String, ByVal trv As TreeView)

    Dim xml_doc As DOMDocument60

    ' Load the XML file into the DOMDocument.
    Set xml_doc = New DOMDocument60
    xml_doc.Load file_name

    ' Add the root node's children to the TreeView.
    TreeView1.Nodes.Clear
    AddChildrenToTreeView trv, Nothing, _
        xml_doc.DocumentElement

End Sub

' Add this XML node's children to the indicated TreeView
' node.
Private Sub AddChildrenToTreeView(ByVal trv As TreeView, ByVal treeview_parent As Node, ByVal xml_node As IXMLDOMElement)
    
    Dim xml_child As IXMLDOMElement
    Dim new_node As Node
    
    ' Examine each XML child. Error 13 Type mismatch - why?
    For Each xml_child In xml_node.ChildNodes
        ' Add the child to the TreeView.
        If treeview_parent Is Nothing Then
            Set new_node = trv.Nodes.Add(, , , _
                xml_child.nodeName)
        Else
            Set new_node = trv.Nodes.Add(treeview_parent, _
                tvwChild, , xml_child.nodeName)
        End If
        new_node.EnsureVisible

        ' Add the child's children.
        AddChildrenToTreeView trv, new_node, xml_child
    Next xml_child
    
End Sub

Upvotes: 1

Views: 269

Answers (1)

Tim Williams
Tim Williams

Reputation: 166511

Not all child Nodes are of type IXMLDOMElement - you also have Text Nodes, comments, etc.

This is for .Net, but you see the basic idea:
https://learn.microsoft.com/en-us/dotnet/standard/data/xml/types-of-xml-nodes

You need to declare xml_child as a less-specific type (maybe IXMLDOMNode)

Upvotes: 2

Related Questions