none
Dynamically Creating Organizational Chart RRS feed

  • Question

  • I have searched the web for an answer to this but came back empty handed. I mean to create an organizational chart dynamically on Word or Excel (not Visio). I have found no VBA commands to assist me in so, only Placeholders which can contain an organizational chart with no info as to how to populate it.

    Any assistance is appreciated.

    Tuesday, September 22, 2015 1:08 PM

Answers

  • Hi S1ngularity,

    >> I mean to create an organizational chart dynamically on Word or Excel (not Visio).

    I am afraid there is no directly way to achieve your requirement. There is no such chart object model to create organizational chart. I think you need to add shapes one by one to create an organizational chart. For example, you could add a Rectangles and fill text within it.

    Here is a simple code about addShape:

    Sub Macro25()
    '
    ' Macro25 Macro
    '
    
    '
        ActiveSheet.Shapes.addShape(msoShapeRoundedRectangle, 636, 216, 83.25, 18). _
            Select
        ActiveSheet.Shapes.addShape(msoShapeDownArrow, 663.75, 236.25, 27.75, 26.25). _
            Select
        ActiveSheet.Shapes.addShape(msoShapeRoundedRectangle, 636, 262.5, 81, 24). _
            Select
        ActiveSheet.Shapes.range(Array("Rounded Rectangle 2")).Select
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Company"
        With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 7). _
            ParagraphFormat
            .FirstLineIndent = 0
            .Alignment = msoAlignLeft
        End With
        With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 7).Font
            .NameComplexScript = "+mn-cs"
            .NameFarEast = "+mn-ea"
            .Fill.Visible = msoTrue
            .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
            .Fill.ForeColor.TintAndShade = 0
            .Fill.ForeColor.Brightness = 0
            .Fill.Transparency = 0
            .Fill.Solid
            .Size = 11
            .Name = "+mn-lt"
        End With
        ActiveSheet.Shapes.range(Array("Rounded Rectangle 4")).Select
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "People"
        With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 6). _
            ParagraphFormat
            .FirstLineIndent = 0
            .Alignment = msoAlignLeft
        End With
        With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 6).Font
            .NameComplexScript = "+mn-cs"
            .NameFarEast = "+mn-ea"
            .Fill.Visible = msoTrue
            .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
            .Fill.ForeColor.TintAndShade = 0
            .Fill.ForeColor.Brightness = 0
            .Fill.Transparency = 0
            .Fill.Solid
            .Size = 11
            .Name = "+mn-lt"
        End With
    End Sub
    

    In addition, you could manually create an organizational chart in excel, record the steps and then modify the code for your requirement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 23, 2015 5:39 AM