Excel with VBA has historically proved a suitable platform for business level reporting. Excel’s user base – across all levels of competency is immense. Presenting data in Excel usually gives rise to the user being able to directly access that data to use in their own analysis, or presentations. The development environment (even aside from VBA) allows for extremely rapid application / reporting development. While there may be more suitable applications for interactive solutions, it can also facilitate the automation of PDFs easily through VBA to assimilate individually tailored reports. This quickly led to creating the Job Family Structure Chart using Smart Art Hierarchy charts, but how to automate it ? Processing starts from the currently selected store running through a cycle of selected stores, along with other macro processing to modify the Training Requirements report, and Training Record Cards, saving these as PDFs. 
1. Replace current smart shape hierarchy shape, with a default new one, resize, and move and delete all nodes except one.
2. Define the top nodes text box.
3. Looping for each of three level 2 job families (nodes);
a. Add a node linked to top level node, defining the text for this family prefixed by “@2” (used later), font, and size.
b. Looping through for each level below level 2;
i. Add a node linked to the node above, with the sub-family or list of departments names prefixed with “@”level.
4. With all nodes defined, the smart are is taking shape, but the formatting doesn’t look great. To get the exact formatting we want, we need to convert the smart art into individual shapes. To do this via VBA, select the smart art and issue the command ;
CommandBars.ExecuteMso ("SmartArtConvertToShapes")
5. For each shape in the resulting GroupItems…
a. If the text begins with “@” (a node – not a shadow shape);
i. Note the level number, and remove “@level”.
ii. Turn off word wrap
iii. Autofit shape size around the text width and height.
iv. Resize and move the shadow to the correct place.
v. If the text contains char string 160 (invisible to see) ;
1. For every such char, make the text around them grey (department is out of scope)
b. Reformat the shadow to appear more 3d, adding lightangle , MaterialMetal, and Bevelcircle formatting and Reformat the shadows lines. 
c. Change the shadow colour to use corporate design standard colours.
d. Make the text box non transparent, with white background.
A final step is just to resize the whole shape set, so the lines joining the nodes are more visible.