CODEWITHSUNDEEP
vbaexceltreeview
Đức Thanh Nguyễn
Đức Thanh Nguyễn

Reputation: 9375

Populate treeview control with hierarchical data from Excel Sheet

I am currently using a treeview control from Treeview Control from JKP. Trying to populate the tree with hierarchical data from an Excel Sheet, I encountered this problem: the tree I populated does not reflect the data. In this picture AP0004 should be under TP0002

Populate a treeview control in VBA

The code I've tried is this

For Each c In Sheet14.Range("A2:A" & Sheet14.Range("A" & Rows.Count).End(xlUp).Row)

    On Error Resume Next
    'Populate level 1
    Set cRoot = .AddRoot(sKey:=c.Value2, vCaption:=c.Value2)

    Set cExtraNode = cRoot.AddChild("ML_" & c.Value2, "Meilensteine")

    'Populate level 2
    Set cNode = cRoot.AddChild(c.Offset(, 1).Value2, c.Offset(, 1).Value2)

    'Populate level 3
    If cNode.Level = 2 Then Set cNode = cNode.ParentNode

    If Not IsEmpty(c.Offset(, 2).Value2) Then
        Set cNode = cNode.AddChild(c.Offset(, 2).Value2, c.Offset(, 2).Value2)
    End If

    'Populate level 4
    If Not IsEmpty(c.Offset(, 3).Value2) Then
        If cNode.Level = 2 Then
            Set cNode = cNode.AddChild(c.Offset(, 3).Value2, c.Offset(, 3).Value2)
            Set cNode = cNode.ParentNode
        ElseIf cNode.Level = 1 Then
            Set cNode = cNode.Child.AddChild(c.Offset(, 2).Value2, c.Offset(, 2).Value2)
            Set cNode = cNode.Child.AddChild(c.Offset(, 3).Value2, c.Offset(, 3).Value2)
            Set cNode = cNode.ParentNode
        End If
    End If
Next

There must be something wrong with my approach here. Any suggestion?

Upvotes: 4

Views: 12899

Answers (2)

Đức Thanh Nguyễn
Đức Thanh Nguyễn

Reputation: 9375

OK, I got it now.I know it's spaghetti code, but for now It works.

For future-people, who may need this

lastRow = Sheet14.Range("A" & Rows.Count).End(xlUp).Row
' root nodes
For Each c In Sheet14.Range("A2:A" & lastRow)
    If cRoot Is Nothing Then
        Set cRoot = .AddRoot(sKey:=c.Value2, vCaption:=c.Value2)
        cRoot.Bold = True
        currentRootKey = cRoot.key
        Set cExtraNode = cRoot.AddChild("ML_" & c.Value2, "Meilensteine")
        currentMLKey = cExtraNode.key
    End If
    If c.Value2 <> currentRootKey Then
        Set cRoot = .AddRoot(sKey:=c.Value2, vCaption:=c.Value2)
        cRoot.Bold = True
        currentRootKey = cRoot.key

        Set cExtraNode = cRoot.AddChild("ML_" & c.Value2, "Meilensteine")
        currentMLKey = cExtraNode.key
    End If
Next
' level 1 children
For Each c In Sheet14.Range("B2:B" & lastRow)
    If Not IsEmpty(c.Value2) And c.Value2 <> currNodeKey Then
        Set cNode = .Nodes(c.Offset(, -1).Value2)
        Set cNode = cNode.AddChild(c.Value2, c.Value2)
        currNodeKey = cNode.key
    End If
Next
'level 2 children
For Each c In Sheet14.Range("C2:C" & lastRow)
    If Not IsEmpty(c.Value2) And c.Value2 <> currNodeKey Then
        Set cNode = .Nodes(c.Offset(, -1).Value2)
        Set cNode = cNode.AddChild(c.Value2, c.Value2)
        currNodeKey = cNode.key
    End If
Next
'level 3 children
For Each c In Sheet14.Range("D2:D" & lastRow)
    If Not IsEmpty(c.Value2) And c.Value2 <> currNodeKey Then
        Set cNode = .Nodes(c.Offset(, -1).Value2)
        Set cNode = cNode.AddChild(c.Value2, c.Value2)
        currNodeKey = cNode.key
    End If
Next

Upvotes: 2

Andre
Andre

Reputation: 27644

I guess it's somewhere around here:

Set cNode = cNode.Child.AddChild(c.Offset(, 3).Value2, c.Offset(, 3).Value2)

cNode.Child simply picks the first child of cNode, not the one you want.

Notice how AP003 is the only node under TP002, because it is the case where TP002 is created.

General note: The global On Error Resume Next might seem elegant to spare you the need of checking if a node already exists, but it will also mask any other errors that might come up.

IMHO a better approach is to always check: does the parent node I need already exist?
If yes, this gets you the node you need to add the child.
If no, create it, and then you have the parent node too.

Upvotes: 2

Related Questions