• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5494
  • Last Modified:

Excel VBA for Organization Chart Add-in for Office

Hi

Does anyone have an example of Excel VBA code for building an org chart in
Organization Chart Add-in for Office
0
Murray Brown
Asked:
Murray Brown
1 Solution
 
broro183Commented:
hi,

Here are some examples of code (mostly links) to build Org charts in Excel, but they don't use an Org Chart Add-in. Try them out & let me know if you have any specific issues.

Radhey provides code: http://xlmix.blogspot.in/
I think this is the clearest example & the code is:
Sub 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

Open in new window



Chandoo suggests using the Google Org Chart Widget: http://chandoo.org/wp/2008/04/15/generate-organization-charts-in-excel-howto/

This MSDN page includes some code snippets & an exe file (I haven't looked at this): https://msdn.microsoft.com/en-us/library/office/aa140203(v=office.10).aspx

This is for Visio but could help: http://blog.bvisual.net/2009/02/24/creating-an-org-chart-without-the-org-chart-wizard/

Near the end of the thread babsannjoy presents his working code: http://www.tek-tips.com/viewthread.cfm?qid=1709060

hth
Rob
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now