My boss has asked me to try to figure out a way to automate our company's org charts. They have been using Visio and would like to continue. So far they just take the previous years drawings and go in and manually do the changes to all of the positions. SO far I have taken all of the employee data, put it into an excel workbook, given each employee a unique ID number, and created a reports to column to put the ID number of the person each employee reports too. With that I can make the org charts in visio by importing data. I have two problems though:
Visio makes orgcharts for every worksheet in the workbook. My boss wants to keep all of the past org chart data in one workbook, so I do not want to recreate every years org charts by importing the excel workbook.
The structure is not very good in Visio. I don't know if there is a way to get Visio to put the boxes in order by how I have them listed in Excel but that would be helpful. I have several people who report to one person, but one of them should be placed higher than the other (so in excel they would be directly the second row). Visio places people randomly through out their level of the hierarchy.
I am new to VBA but have done a few small projects with it and a lot of research. I am wanting to know if anyone has any idea how to use VBA to create structured org charts in Visio using Excel data. (Also we have the 2016 excel version but only the 2010 Visio version so that has been a bit of road block). Any help on this would be greatly appreciated!
The Visio OrgChart wizard can round trip data. You can use an Excel file to create a Visio OrgChart, modify it and then export the information back to Excel but... The OrgChart wizard randomly places the individuals on the page so whatever order you tried to put in the Excel file is lost. There is a 3rd party OrgChart wizard that will respect your order, but I do not have the details at hand. As to the version issue, you will have to save the Excel file in an older format that Visio 10 can handle.