Creating an Organization Chart using a List of Employees

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Lisa Wollin
Microsoft Corporation

April 2001

Applies to:
   Microsoft® PowerPoint® 2002
   Microsoft® Word 2002
   Microsoft® Excel 2002

Summary: The Microsoft Office 10.0 Object Libraries include new objects for creating diagrams in Microsoft Word, Microsoft PowerPoint and Microsoft Excel. This article discusses those new objects and demonstrates how to create an organization chart from data in a database. (7 printed pages)

Download ODC_CreateOrgCht.exe.

Contents

Introduction
Creating Simple Diagrams
Adding Nodes
Adding Text to Nodes
Creating an Organization Chart from an Employee List
Conclusion

Introduction

In the days of the MS-DOS® operating system, I remember creating organization charts and diagrams in a drawing program, and then cutting (with scissors) and pasting (with tape) their printouts in order to get them into a paper document. Of course, the documentation for the drawing program explained how I could save the drawing and import it as a graphic, but it never worked (or looked) quite right.

Then with the Microsoft® Windows® operating system came the ability to more easily cut a diagram from a drawing application and paste it into my document. And importing a graphic saved in a drawing application was significantly easier and more attractive in a Windows environment than it was in an MS-DOS environment. But it was still a multiple-step process.

Now, with Microsoft® Office XP, diagramming is native to Microsoft® Word, Microsoft® PowerPoint®, and Microsoft® Excel, so adding diagrams to your documents, presentations, and spreadsheets is much easier than before. In this article you will learn how to create diagrams programmatically by using the diagramming feature built into the Word and PowerPoint 10.0 Object Libraries.

Note

   

The Excel 10.0 Object Library is slightly different; the code samples in this article work only in Word and PowerPoint without alteration, unless otherwise noted.

Creating Simple Diagrams

Use the AddDiagram method of the Shapes collection to create an organization chart, or a cycle, pyramid, radial, target, or Venn diagram. The only difference between PowerPoint, Word, and Excel when creating a diagram is how you access the Shapes collection for these applications. In PowerPoint, you access the Shapes collection from the Master object, the Slide object, and the SlideRange collection; in Word from the Document object and the HeaderFooter object; and in Excel from the Worksheet object and the Chart object.

The following function allows you to create a base diagram in any of the three applications. All you need to do is pass in a Slide, Document, or Worksheet object, and it returns the created Shape object to the calling subroutine. However, if you run the code, you won't see anything except a blank slide, page, or worksheet. That's because after you create the base diagram, you need to add nodes to the diagram.

Note

   

In this article, I use "base diagram" to mean a diagram without nodes to distinguish it from a diagram with nodes.

Function CreateDiagram(ByRef objDocument As Object, _
      ByVal DiagramType As MsoDiagramType, ByVal intLeft As Integer, _
      ByVal intTop As Integer, ByVal intWidth As Integer, _
      ByVal intHeight As Integer) As Shape

   Set CreateDiagram = objDocument.Shapes.AddDiagram _
      (Type:=DiagramType, Left:=intLeft, Top:=intTop, _
      Width:=intWidth, Height:=intHeight)

End Function

You can call this function from any of the three applications using one of the following lines of code.

Calling the CreateDiagram Function in PowerPoint

Set shpVariable = CreateDiagram(objDocument:=ActivePresentation _
   .Slides(1), DiagramType:=msoDiagramRadial, intLeft:=50, _
   intTop:=50, intWidth:=100, intHeight:=100)

Calling the CreateDiagram Function in Word

Set shpVariable = CreateDiagram(objDocument:=ActiveDocument, _
   DiagramType:=msoDiagramRadial, intLeft:=50, intTop:=50, _
   intWidth:=100, intHeight:=100)

Calling the CreateDiagram Function in Excel

Set shpVariable = CreateDiagram(objDocument:=ActiveWorkbook _
   .Sheets(1), DiagramType:=msoDiagramRadial, intLeft:=0, _
   intTop:=0, intWidth:=100, intHeight:=100)

Adding Nodes

Once you've created the base diagram, use the AddNode method of the DiagramNode object to add nodes to the diagram. With all types of diagrams, except radial diagrams and organization charts, all nodes in a diagram are siblings. Therefore, when you add nodes to the base diagram, you add them as children of the diagram. However, when creating a radial diagram or an organization chart, you add a single top-level node to which you add child nodes.

The following function adds the specified number of nodes to the specified diagram. You'll notice that if the type of diagram being created is a radial diagram or an organization chart, the code creates one top-level (or parent) node and then adds the remaining nodes as child nodes to that top-level node. If the specified diagram is any other type of diagram, it adds the specified number of nodes as children of the specified diagram.

Function AddDiagramNodes(ByVal shpDiagram As Shape, _
      ByVal intNumNodes As Integer) As Boolean

   Dim dgnChild As DiagramNode
   Dim intNodes As Integer

   On Error GoTo Error_Handler

   'Determine type of diagram and add first node if necessary.
   Select Case shpDiagram.Diagram.Type

      Case msoDiagramOrgChart, msoDiagramRadial
         'Add first node.
         Set dgnChild = shpDiagram.DiagramNode.Children.AddNode

         'Add remaining nodes as children of the first node.
         For intNodes = 1 To intNumNodes – 1
            dgnChild.Children.AddNode
         Next intNodes

      Case Else
         'Add first node.
         Set dgnChild = shpDiagram.DiagramNode.Children.AddNode

         'Add remaining nodes.
         For intNodes = 1 To intNumNodes - 1
            dgnChild.AddNode
         Next intNodes

   End Select

   AddDiagramNodes = True

Exit_Sub:
   Exit Function

Error_Handler:
   AddDiagramNodes = False
   Resume Exit_Sub

End Function

Notice that the code inside the For…Next loop of each case is slightly different. While the first case creates a single top-level node and adds the remaining nodes as children of the first node, the second case adds the first node and then adds the remaining nodes as siblings (not children) of the first node. This is important to remember because if you attempt to add all the nodes for a radial diagram or an organization chart as child nodes of the diagram, you will receive an error. At the same time, you will also receive an error if you create a pyramid diagram with one node and then try to add child nodes to it.

You could use the following line of code to access this function and add the specified number of nodes to the specified shape.

Call AddDiagramNodes(shpDiagram:=shpVariable, intNumNodes:=4)

Adding Text to Nodes

Now that you have a diagram, you will want to add some descriptive text to the nodes. DiagramNode objects have a TextShape property that accesses a Shape object. From this Shape object, you can access the TextFrame object. Next, you would use the TextRange property of the TextFrame object to access the text and text properties for the nodes. Then use the Text property of the TextRange object to insert text into the diagram node.

**Note   **In Excel, you would use the Characters property of the TextRange object to access the text and text properties for the nodes and the Text property of the Characters object to insert text into a diagram node.

The following subroutine adds text to an existing diagram node and formats the new text.

**Note   **This subroutine will work only in PowerPoint and Word.

Sub AddTextToNode(ByRef objDiagramNode As DiagramNode, _
      ByVal strNodeText As String, _
      Optional ByVal intFontSize As Integer = 10, _
      Optional ByVal strFontName As String = "Tahoma", _
      Optional ByVal blnBold As Boolean = False, _
      Optional ByVal blnItalic As Boolean = False)

   With objDiagramNode.TextShape.TextFrame
      With .TextRange

         'Add text to node.
         .Text = strNodeText

         'Format new text as specified.
         With .Font
            .Bold = blnBold
            .Italic = blnItalic
            .Name = strFontName
            .Size = intFontSize
         End With
      End With
   End With

End Sub

You could use the following line of code to access this function and add text to the specified node.

Call AddTextToNode(objDiagramNode:=dgnNewNode, _
   strNodeText:="This is text for a node.", _
   intFontSize:=8, blnBold:=True)

If you want to format the font of the text, you would access the associated Font object's properties and methods. The above subroutine uses the Size, Name, Bold, Italic, and Underline properties of the Font object to format font attributes, and uses the WordWrap property of the TextFrame object to specify whether words wrap onto multiple lines within the node's text frame.

Creating an Organization Chart from an Employee List

Now that you know how to create a diagram, I'll show you how to create an organization chart from a list of employees. This solution, available as a download, is comprised of one main routine, one supporting subroutine, and five functions.

  • CreateOrgChart: The main subroutine that calls the other subroutines and functions.
  • GetData: Connects to the data source and returns an ADO Recordset object.
  • GetReports: Clones the main recordset and filters for an employee's direct reports.
  • CreateDiagram: Creates a base diagram of the specified type.
  • AddNodes: Iterates through the recordset to add the necessary nodes and recursively adds direct reports to each node added.
  • AddNewNode: Adds the individual nodes.

To start, you need to add a reference to the Microsoft ActiveX Data Object 2.5 Library (ADO) to access the records in the database. To hold the employee information, I used a Microsoft Access database with three fields: the employee's name, title, and manager's name. However, you could use a Microsoft SQL Server-based database or other database. To do so, you would need to specify the connection string for your alternate data source using the strActiveConnection variable in the CreateOrgChart subroutine.

There is one global variable for the main recordset. Because the GetReports function filters the records in the recordset and then alters the original recordset, the code would need to access the database every time it needed to work with all of the records. To eliminate this, the code creates a main recordset and the GetReports function clones the main recordset to filter it and return only the employees directly reporting to another employee.

I created a constant in the CreateOrgChart subroutine called TITLE_FIRST_NODE for the title of the employee who should exist at the top of the chart. This assumes that there is only one person in the database with this title. Since there can only be one top level node, if there were more than one person with the same title, you would receive an error when the subroutine attempted to create additional top-level nodes. However, you can get around this by using the person's name instead of his or her title. Then the solution would start with that person and create an organization chart for their part of the organization only.

Once the first node is added, the remaining nodes are added. The GetReports function clones the main recordset and then filters it to select all records where the ReportsTo field equals the value of the Name field of the previously added node. If the number of records in the returned recordset is greater than zero, the code adds nodes for the direct reports. However, because a direct report may also have their own reports, the AddNodes subroutine recurses as many times as is necessary to add nodes for all reporting employees.

The AddNewNode function adds both the main parent node and each child node. In addition, it inserts the name and title into the node's Text property. The optional eNodeType argument, which is a custom enumerated constant, identifies whether the node is a top-level, assistant, or child node. The only difference between an assistant node and a child node is that when the AddNewNode function creates an assistant node it specifies the NodeType argument for the AddNode method as msoDiagramAssistant. This creates a node that is called out from the other nodes as an administrative assistant would be called out on an organization chart.

Conclusion

Put away your scissors and glue and get rid of your old drawing applications. With native diagramming support in your Office XP applications, getting diagrams into your documents has never been easier.