Visio VBA Org Chart Automating the Synchronized Copy Function

[raesnard]

Visio - Using VBA to do the following to an org chart:

1 Copy a master shape including its subordinate shapes
2 Create a new page (this can be done)
3 Paste the master and its subordinates onto the new page (with a link back to the original page)
4 Hide the subordinates on the original page with a link to the new page on the master shape

The Synchronized Copy performs this function; however, the
presented dialog box requires responses that I have been unable to write code
to answer. 

Windows 10

Microsoft Visio Professional 2019

Microsoft Office Professional 2016

[wapperdude]

This could be done.  But not sure it's the best approach.

1) Presumably you need to do this a lot, hence VBA.  If it's just a half-dozen times not sure it's worth effort to write the code.

2) Would merely copying the master shape and adding it to a new, preceding page be acceptable, thus avoiding and hiding the sub-ordinates?

3) Would placing all masters on a single page be acceptable?  Each master would be linked to appropriate page of his organisation?  Each subpage could be linked back to the page of masters.

[raesnard]

Hi thanks so much for the reply.  Sorry it took so long to respond... just a lot going on.

Really looking to create a repeatable process... the Orgchart is large.  Going through it each time to create the synchronized copy is supper annoying.  It is frustrating that Microsoft didn't make it so you could systemically use the Synchronized copy so you could code a response to the dialog box.  If they did this automation would be super easy.

Any hints as to how to start writing this code? I am an expert at Excel... but visio  is a different animal.
THANKS!

[wapperdude]

As no one has jumped on this, I'm thinking additional info is needed, or people thought you'd moved on.. 

Basically, if you could supply a small example of what your end-game looks like, then it would be easier to advise how to proceed.   Also, you didn't specifically comment on my 2nd or 3rd points above.  Those were intended to get better understanding of your needs and possible alternatives.

Finally, there may be alternative product solutions out there.  Some may already do what you want.  Check out this link:  https://theorg.com/wiki/the-best-organizational-chart-software

[raesnard]

Hi appreciate the response! 
Copying the master shape and all of its subordinate shapes to a new page would be an amazing start.   Once I do that I am pretty confident that I could loop back and hide the subordinates on the original page. This might do the trick...

Not sure about the all masters on one page... in a complex multi-tiered organization that might not be doable. 

I am looking to create an automated process to produce a weekly orgcharts based in a constantly changing population.  Here would be a sample workflow:
1) Create data necessary to produce orgchart the output is an excel file with the needed information (this is complete)
2) Import the data into Visio
3) Run process on Orgchart to create the separate pages (subcharts), to make it printable / visible by team - this is the part that I am struggling with

Does that help?   
Thanks!

[wapperdude]

That helped a lot.  As you stated, this is a two part process.  (1) import data, (2) configure Org Chart.

You can expedite much of (1) if you take one of your existing org charts and use the Org Chart Wizard to export the org chart and create an Excel file.  This file will be the template for your data.  Edit this file as necessary to incorporate updates.  Then, open a new Visio org chart template and import this file.  The Wizard import process builds a completely new file.  It will ask you about your "Executives only page", tell it to exclude.  Alternatively, before you do your export to build the Excel, delete the Executive's Page from Visio.  Otherwise, the Wizard will create additional page, one for each executive on the Executive's page.  You don't want that.  When complete, you will have a new, populated file with all the charts, each on its individual page.  You won't have to do all that copy / pasting.  Plus, you only need to export once.  As long as you maintain the format of the Excel file, it ought to faithfully produce the desired  updated Org chart.

For part (2), my example file has a macro.  The macro is also listed below. There is some preliminary editing you'll have to do, but it's almost plug n play.  The file has many comments in it.  Anyway, it should give you an idea of how to go about this portion of the task.

The attached file was generated using the steps given above.  You may want to try things on a simple file.  If you open a VBA window (accessed under developer tab), and place it side by side with drawing window.  Click the mouse anywhere in the macro to establish "focus", and then you can use <F8> to step thru the code and watch what it does.

Here's the code for the macro:

Code Select Expand


Sub Macro2()
'First, setup Org chart.  Good method is Org Chart Wizard Inport from Excel file
'Run macro.
'Macro will add new 1st page, named Execs.  It will search thru all pages to find shapes that have
'Master = name1.  Name1 must be defined below before running macro.  If a qualified shape is found
'the macro will paste it onto the added page using Drop method.

'Some editing is needed to provide a more sophisticated method of modifying the Drop command

    Dim vsoPg1 As Visio.Page
    Dim vShp As Visio.Shape
    Dim execShp As Visio.Shape
    Dim name1 As String
    Dim xPos As Double
    Dim hOff As Double
    Dim yPos As Double
    Dim pgCnt As Integer
    Dim i As Integer
   
    xPos = 2
    yPos = 7
    hOff = 0
   
    pgName = "Execs"
   

'Add new first page
    For Each vsoPg1 In ActiveDocument.Pages  'check see if exists and delete it
        If vsoPg1.Name = pgName Then
            vsoPg1.Delete (1)
        End If
    Next
   
    Set vsoPg1 = ActiveDocument.Pages.Add
    vsoPg1.Name = "Execs"
    vsoPg1.Background = False
    vsoPg1.Index = 1
   
    pgCnt = ActiveDocument.Pages.Count
   
    For i = 2 To pgCnt
        ActiveWindow.Page = ActiveDocument.Pages.Item(i)
        ActiveWindow.DeselectAll
        name1 = "Executive Belt"
        For Each vShp In ActivePage.Shapes
            If Not vShp.OneD Then
                If vShp.Master = name1 Then
                    hOff = vShp.Cells("Width").Result(visInches)
                    ActiveWindow.Page = ActiveDocument.Pages.ItemU("Execs")
                    Set execShp = ActivePage.Drop(vShp, xPos, yPos)
                   
'Need improved method to update drop location
                    xPos = xPos + 1 + hOff
                    yPos = yPos + 0
                    ActiveWindow.Page = ActiveDocument.Pages.Item(i) 'return to active search page
                End If
            End If
        Next
    Next
   
End Sub


[raesnard]

Wow... thank you so much... this definitely is a step in the right direction.   I never thought to export the data and re-import! 
Need to think about this... what would be amazing is if I could control the page breaks right from the exported data file before re-importing it.   
If you have any ideas about that it would be great.
I need to play with what you have provided!
Thanks again!

[wapperdude]

Ah!  The exuberance of discovery! 

The quick answer is it does do the page breaks.

The quick test...
Download both files.  Open Visio and delete all pages.  You'll be left with single blank page.  Run the import wizard using the Excel for data source.  Viola!