Advice for maintaining Org Charts

[WCTICK]

I am trying to determine the best method for maintaining and updating an Org Chart with the following characteristics:

Approximately 700 employees and currently displayed on 54 pages.
Quite a high frequency of "Reports To" changes.  Within the last 6 months, there were slightly over 50 employees whose Reports to manager changed.
Position Number is used as our Unique ID.
A lot of layout tweaks are necessary to get all the employees to fit on the appropriate page with a readable format.
An Excel spreadsheet containing the organizational data that is generated from a Cognos report is used to refresh the Org Chart periodically.

Initially, I tried linking the data to the shapes and then refreshing the data with the updated spreadsheet.  That maintained the layout we had, but the reports to changes required a lot of manual work, to move the shapes to the new manager and new/page.  If there is a way to automate the updating of employee shapes where the reports to manager has changed, I am not aware of it.

Then, I decided to use the Org Chart wizard with a VBA script to update the data from the spreadsheet and specify the managers to put at the top of each page and also to insert the custom page names we had created.  This updated the chart data but didn't retain the customized layout.  I then had to go through and reapply layout changes to many pages... which also took a lot of manual work.

I am not proficient with Visio concepts or VBA coding, but feel I must be missing something that would address these issues and provide a simpler method for maintaining the Org Chart over time.

I hope this is appropriate to post in this forum... if you have any advice, please let me know!  Thank you.

[Croc]

Several years ago I had a similar case. I was asked to automate the process of updating an org chart containing approximately 140 departments and 420 employees. Frequent updating of the organizational structure was expected.
The source data for constructing the diagram is located on the SQL server.
The task was set to ensure convenient updating of the diagram.
The following decisions were made:
1. A special display form was used, more compressed than in a regular org chart.
2. Special software (macros) has been developed to ensure comparison of the org chart with the database and partial automation of changes. The chart is not redrawn every time the data changes. Instead, changes are marked with a special marker and displayed in the protocol. After this, the operator interactively makes changes to the diagram. In some cases this is done automatically, in others using manual operations.
A few years later, the customer informed me that the solution was being used successfully. I also think that the solution turned out to be quite convenient.
An example of chart maintenance can be seen in the video.
https://drive.google.com/file/d/1S5HAOeZtfUCeV_-kNFvXBcuqugKqlqF1/view?usp=sharing

[WCTICK]

Thank you very much for your response.

Our security software has prevented me from viewing the video initially.  I have submitted a request for temporary access and look forward to watching.

[WCTICK]

i was able to get the video to play, but I can't get sound... should there be?

Thanks.

[Croc]

No, this file does not contain an audio track