Sunday, April 18, 2010

Generate organization chart in excel

here is a good way to generate organization charts in excel using SmartArt and little bit of VBA.

well i am no expert and what ever i do is all hit and trail with lots of reference from help contents.

so logic of creating organization charts using smart art is as follows.

but before that few facts for easy understanding at least this helped me..
- Every Smart art has a Layout
By default excel has many default smart art layouts, we will use layout number 92 in office 2010 as it is one of the organization layout, feel free to increase or decrease the number to change layout.
- SmartArt has nodes
- All nodes can be referred as nodes
- We can count the number of nodes
- Nodes can be promoted or demoted
- Nodes have levels. like level 1,2,3 etc for each horizontal representation.

Now enough mentioned, time of logic.

logic is to use vba macro to insert a SmartArt organization chart layout, then delete all its default nodes, then add required number of nodes, promote all the nodes to one level and the use loops to promote and demote each node on the desired level.
so we need 2 things primarily Name + designation & Level, lets use the following as reference data. (fake Data)


its a good idea to combine Name and Designation using the concatenation function so that Name is in the first line and Designation in the subsequent line. eg: 
Steve
Director
i used the Ascii code for 'enter key' to achieve the same with concatenation function.
=Concatenate(B2,char(10),C2) 
on mac pc the code is char(13). so use accordingly. now here is my updated table, i have emerged, column B and C in to one column, also i have removed the first row, the header row.

 now simply click the record macro button and record and macro, i gave it a name Org and shortcut key as ctrl +j. then click stop, we only wanted to record a blank macro

 now got to view macro and then edit macro, this will open VBA editor, now all you need to do is to replace the existing function with the one mentioned below.
 ----------------------------------------------------------------------------------
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


 ---------------------------------------------------------------------------------------------------
now simply run the macro and all will done automatically, here is the output form my code.

that's it i can attach the file as well however that's a .xlsm file which might get blocked due to macros. give it a try and let me know if this doesn't work. i will be glad to help.

13 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Not working properly. Specially hierarchy is not maintained.

    ReplyDelete
  3. I am getting this error
    While QNodes(Range("A" & i)).Level < Range("C" & i).Value

    This index to specified columns is out of bound ? Can you please help.

    ReplyDelete
  4. JUST DELETE THE HEADERS I WILL DEBUG SAVE AND RUN

    ReplyDelete
  5. hi Gupta, is there a way to get the XL file directly (my email is: tilouhelvet @ gmail.com). Thx so much.
    Further, if I want to use that for an ownership structure with recursive ownership, will that work?

    Thx

    ReplyDelete
  6. Hello Gupta, would be possible to add pictures using some other (#91?) layout?

    ReplyDelete
  7. Hello Gupta,
    This is great post. Thanks a billion for doing this.
    Could you help further please? Sometime, employees are two or three or 4 level under one person. How would you reflect this? E.g: Level 1 = Director, Level 2 = Head of xxx, Level 3 = nothing, Level 4 = nothing, Level 5 = Guardians.
    Thanks in advance. You could also reply to me via joel@seedmadagascar.org
    Cheers

    ReplyDelete
  8. I forgot to click on "Notify me"

    ReplyDelete
  9. Was having an issue where the nodes of depth 2 were not aligned correctly. This corrected the error:

    While QNodes(Range("A" & i)).Level > Range("C" & i).Value
    QNodes(Range("A" & i)).Promote
    Wend

    ReplyDelete
  10. Hi Gupta, thanks so much for sharing this. It's great work, very helpful. I was having trouble to run it, but I figured it out it was my excel has different chart layout. I replace this line of code:
    Set ogSALayout = Application.SmartArtLayouts(92)

    for this one

    Set ogSALayout = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2005/8/layout/hierarchy1")

    and worked perfectly. Thank again!

    ReplyDelete
  11. I am unable to run the macro. Getting some error message and i dont know basics of macro. Kindly share the template if you can please to nivedi.krish@gmail.com

    ReplyDelete
  12. Pleae can any one share working VBA macro for this o\with me :klnsunilkumar1@gmail.com

    ReplyDelete
  13. it doesnt work to give the hierachy tree. i only see 2 big circles. One with Steve name the other cirlce inside are a nodes of name. thank you

    ReplyDelete