excel - 组织架构亲子关系

Question

我正在为一家公司建立一个组织结构。但是，我当前的 VBA 脚本没有父子关系，例如（Team Leader 下的 Trainer）。如何在宏观中创建关系并将其显示在组织结构中？在下图中，Jodan 和 Sky 应该在 Jane 之下。VBA代码：

 org()
 org Macro
'Macro to generate organization chart
'
' Keyboard Shortcut: Ctrl+j
'
    Dim ogSALayout As SmartArtLayout
    Dim QNode As SmartArtNode
    Dim QNodes As SmartArtNodes
    Dim t As Integer
    Set ogSALayout = Application.SmartArtLayouts(92) 'reference to organization chart
    Set ogShp = ActiveWorkbook.ActiveSheet.Shapes.AddSmartArt(ogSALayout)
    Set QNodes = ogShp.SmartArt.AllNodes
    t = QNodes.Count

    While QNodes.Count < t
    QNodes(QNodes.Count).Delete
    Wend

    While QNodes.Count < Range("A1").End(xlDown).Row
    QNodes.Add.Promote
    Wend

    For i = 1 To Range("A1").End(xlDown).Row
    'Promote and demote nodes to put them at the proper level.

    While QNodes(Range("A" & i)).Level < Range("C" & i).Value
        QNodes(Range("A" & i)).Demote
    Wend

    'Copy the cell text to the node.
    QNodes(Range("A" & i)).TextFrame2.TextRange.Text = Range("B" & i)
    Next i
End Sub

标签： excelvba