Microsoft Office Visio has had an Org Chart Wizard for as long as I can remember (and I have been using Visio since 1996). It has gone through several iterations, but basically it provides a simple way to create personnel hierarchies from a variety of data sources. It even has a command line interface so that the charts can be created with a minimum of user interaction. This is great, but it is limited. Firstly, it does not take advantage of the new Link Data to Shapes and Data Graphics in Visio 207 Professional; secondly, it only does direct reports with just one type of layout; and thirdly, you cannot refresh the diagram easily. Therefore, I thought it would be interesting to overcome some of these obstacles using the new features of Visio 2007 Professional and with a little code.
Visio installs a sample OrgData.xls Excel spreadsheet in the folder <Program Files>Microsoft OfficeOffice12SAMPLES1033 by default, so I have copied this file and slightly amended it.
There are plenty resources on the web to help you use the built-in Org Chart Wizard in Visio, for example : https://support.office.com/en-gb/article/create-an-organization-chart-in-visio-abb3dd17-2692-439f-9945-3a015767d96a . If you follow these in instructions, then you can produce a chart similar to the one shown below. I have used Color By Value to automatically fill the shapes with different colors by Department.
Applying more than the Color By Value Data Graphic can be problematical because these shapes are rigorously controlled by the OrgChart addon, and I have often seen questions about modifying the appearance or layout in the newsgroups. Customization is also complicated by the fact that there are many master shapes in the stencil, each of which can be changed to look like one of the other. For example, an Executive can become a Consultant, or a Position can become a Manager. This is done with the Change Position Type tool, but this does not magically swap one shape for another, but it just changes a property that changes the shapes appearance. You may think that this is not a problem, except that any customization requires that it be done to all of the master shapes, not just one.
I slightly modified the spreadsheet to insert “mailto:” before the email address. This will enable Link Data to Shapes to automatically understand that it is a hyperlink and create one on each shape.
I used Link Data to Shapes to a display the spreadsheet in an External Data Window in Visio. Then I simply drew an ellipse and modified its fill, line and shading before dragging one of the records onto it.
Once there is some data on the shape, you can use the Data Graphics in Visio 2007 Professional. I linked the Department to Color By Value again; Master_Shape to the built-in face icons, the Office_Number to a circle at the bottom, and Title, Name and Department as three lines of text in the center of the shape. The MailTo values automatically create an hyperlink on each shape, as hoped.
After showing the Document Stencil, I dragged the shape onto it, and named the new Master as Person.
You can then use this Person master to drag and drop the records from the External Data Window onto the Visio page. Interesting, but not a very pretty organization chart.
Now comes the clever(ish) bit. The data recordset in the External Data Window is accessible by code. Therefore, you can loop through the records, get the employee name, then check which other employees report to this employee. Having got the sub-ordinate employees, you can add a connector between them.
Just for good measure, I take the opportunity to name the shapes, as I loop through them. This makes the Drawing Explorer Window usable.
The download includes the ConnectSubordinates sub routine in the ConnectShapes module with its support functions, getColumnIndexByName; and IsArrayAllocated.
So, now we have a connected diagram. Not very pretty, yet, but it is connected.
You can manually use Shape / Configure Layout to alter the appearance of the chart
Alternatively, you can automate the layout with code. I have included macros in the LayoutShapes module for changing the appearance in the download, for example, LayoutPageCircular creates a diagram like this:
You can now choose the type of chart to suit your requirements, and, of course, Visio will automatically optimize the layout if you add in extra matrix reporting.
I have included a short macro, DeleteConnectors, so that the links can be recreated when the data is refreshed. Of course, all of this can be fully automated, along with publishing to the web, PDF or Xps.
The sample files can be downloaded from here: