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 : About Organization Chart Wizard data and Create an Organization Chart in Visio . 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:
Visio : Link Data OrgChart.vsd
Excel : OrgData.xls
Visio 2010 MVP Session videos reprise
Back in 2012, my fellow Visio MVPs, Scott Helmers and Chris Roth, and I recorded a series of 24 videos about Visio 2010. They were first hosted on Microsoft’s web site, then they put them up on YouTube, they they got deleted :-(. Well, we have managed to retrieve them, and put them back up…
Using Visio Org Chart Wizard to work with Exchange Online
I have used the Microsoft Visio Organization Chart Wizard many, many times over the years, but I usually use it from text files, and I had been aware that I was not getting good results from the Microsoft Exchange Server directory option, now that I use Office 365. I was always getting just the first…
Visio news from MS Ignite 2019
Microsoft Ignite 2019 at the huge Orange County Conference Conference Center in Orlando is over for another year. 30,000 attendees over 6 days, and I have seen my first sunlight today since I arrived in Florida last Saturday! So, it is time to review just what was announced about Visio, and how much Visio is…
Clarity Videos Re-boot
A series of 6 videos by Harry Miller from 2009 showing off various aspects of Visio!
Replacing Images in Visio Shapes by Changing Data
Learn how you can replace an image in a shape using a Shape Data value and a macro
Visio in PowerBI for viewing personnel hierarchies and locations
My last article, Aliasing Data Columns for #Visio Organization Chart Wizard , mentioned the slight anomalies in the default Shape Data rows for the Organization Chart shapes and the Resources / Person shape. This prompted me to create an alternative view of the same data that can be linked to the Person shapes on a…