Shapes Relations Manipulation Tool

[Yacine]

Here are the visio files.

[Paul Herber]

I'm speechless. Incredible.

[Bald Eagle]

Hi Yacine - I just came across this thread now, and I can see that you put an awful lot of time, talent, and creativity into this.
I'm sure it will take some time for me to look at it, play with it, and fully appreciate what you've accomplished!   
You do nice work - you're quite an asset to your company and this forum. <highfive>

[Yacine]

Thank you very much.
I actually had much more ideas to implement in the tool and make it easier to use (Specially a possibility to save and load scripts).
But I lost my motivation due to the lack of response from the community.
Hopefully, I'll get again bored enough, during the next holidays, to finish the job. 
Regards,
Y.

PS: I'll be busy with other projects in the upcoming months, but if you're willing to play, you may check some of the scripts I collected. The attached file contains also some development notes, that show where the project is aimed to go.

[jmw_visio]

UNBELIEVABLE !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Great Work! Inspiring!

[Yacine]

A big step toward a usable tool: the tool has now a storage feature, allowing to re-use scripts, without having to retype them in the tool.
As fan of Access I opted for an Access-Database to store the scripts. For those of you who don't have this software, a runtime dll can be loaded from here: https://www.microsoft.com/de-de/download/details.aspx?id=39358
I probable should have used a faster and lighter way - but you know me as lazy. 
The second upload is the DB itself with a collection of scripts I made.
Many scripts don't work properly and need editing. Well now you can do the editing by yourselves and keep the work.
Others scripts (like the text formatting, or the gap scripts) work perfectly and are very useful.

The tool is in the attached stencil. As usual: drag the square on the drawing to start the tool.
Make sure to save the DB in the same directory as the stencil.

Enjoy,
Yacine

[Yacine]

The DB

[J_M]

Hello Yacine,

Thank you so much for this interesting thread. Please note that I am new to VBA and Visio, and I am trying to automatically generate a container/box view from an imported Excel in Visio.
Attached is a template of the Excel, and template of the output view in Visio, and the code that i did so far (which is not generating the correct outcome)

First part of the code available under folder Modules (Module1):

Code

Option Explicit

Sub main()

Dim xlApp As New Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWsh As Excel.Worksheet
Dim r As Long
Dim m As Long


Set xlWbk = xlApp.Workbooks.Open(("D:\OneDrive - Murex\Documents\Barometer IT\Temp\Template Excel.xlsx"))
Set xlWsh = xlWbk.Worksheets(1)

' Last filled row in column A
'm = xlWsh.Range("A65536").End(xlUp).Row
m = 4


For r = 2 To m
Dim shape As Visio.shape

    Dim cont As Container
    Set cont = New Container
    cont.InitializeClass xlWsh.Cells(r, 2).Value
    

Next r

    
    xlWbk.Close SaveChanges:=False
xlApp.Quit

End Sub

Second part of the code available under folder Class Modules(class "Container"):

Code

Option Explicit
Public mName As String
Public mContainerShape As Visio.shape



Public Sub Class_Initialize()
    
    Set mContainerShape = createcontainer()
    
    'Set mSubcontainers = CreateObject("Scripting.Dictionary")
End Sub

Public Sub InitializeClass(pName As String)
    'Initialize with Setter so that container text title will be set too
    mName = pName
    mContainerShape.Text = mName
    
End Sub


Public Function createcontainer(Optional shape As Visio.shape = Nothing) As Visio.shape
    Dim Container As Visio.shape
    Dim doc As Visio.Document

    Set doc = Application.Documents.OpenEx(Application.GetBuiltInStencilFile(visBuiltInStencilContainers, visMSUS), visOpenHidden)
    Set createcontainer = Application.ActivePage.DropContainer(doc.Masters.ItemFromID(2), shape)
    
    doc.Close
End Function

I really appreciate your help and your assistance.
Please let me know for any additional information needed.

Many thanks

[Yacine]

So I managed to solve the task. But it was harder than expected and I'm hesitant about how adequate the solution will be for you. It just requires so much know-how.

I wrote the tool in the deepest possible imaginable level of concentration and 2 years later am barely capable of reminding the details of its implementation.

One most important point is that I did not complete all the import possibilities the tool should have deserved. It had a routine in which you can select the parent/child branch and set it up to get the dependency fields filled, but all the imports from trees, files, drawings have never been implemented.

So I could have left you with the fact that you'd need to set dependencies manually (with the tool), but the task was to generate the drawing from an Excel file. So I wrote a routine that lets you choose a file, then generates automatically the dependencies.
I had however to modify the Excel file in so far as I added a column "direction" and I transformed the data range in a table. The "ParentID" column had to be renamed in "ParentID_" as my tool is already using it based on shape IDs.

The drawing itself "should" have been automated, but I opted to use the Sharem tool instead. Why? Because setting up the drawing on the basis of scripts is so much easier than hard coding them. Having scripts - and maybe several of them - gives you so much more flexibility.

So far as introduction. Now on working with the solution.
Run the macro "load_excel". It will ask you for a file to load, then it will draw rectangles and assign them both the properties of the excel file and additionaly those required by the tool.

Now you can apply the positioning (and other) scripts.

For the boxes in boxes task, I set up the script "BoxInBox3".

As for the labeling you can start from the script "insert Text2" to insert the texts that suit you the best.

The coloring was made with the "TINT2" script.

So far for my work.
I thank you for the opportunity to check and debug my work and would be very pleased if my tool will be used in usefull manner.

Cheers,
Y.

PS: I uploaded a new version of the "relations.vss" stencil. It got some minor bug fixes.
The Access-DB contains the a.m. scripts and the Box-in-Box.vsdx contains the macro to import and process the Excel data.
Don't hesitate to ask, if you need more help.

[Yacine]

The drawing with import macro and the modified excel file ...

[Yacine]

The updated Stencil for the main tool  ...

[Yacine]

And finally the DB containing the scripts.

[J_M]

@Yacine,
Amazing! thank you so much for your time and help!

Just a small question, in your comment you told me to run the macro "load_excel". I tried to look for it in the list of macros within Relations stencil, but couldn't find where it is exactly (attached screenshot of the list i have)

Additionally, in the Excel file I would like to load, i have to add manually a column "direction" before i load it? if yes, what should i include in this column?

After running the above mentioned macro, i am supposed to also run some of the scripts you mentioned? ("BoxInBox3", "insert Text2", "TINT2", etc.)

Thanks again for your assistance, much appreciated

[Yacine]

1) Silly me! VSDX doesn't store macros. I'm attaching now a VSDM.
2) The direction column will help you to automatically set the desired arrangement direction for the item's children. 1 = columns / 0 = rows.
3) BoxInBox3 is the actual macro to arrange the shapes. Insert text and Tint are "embellishment" scripts - not really needed.
BTW, I found a small bug in the BoxInBox script.
Here's the corrected version

Code

IF PinX exists THEN
LocPinX := 0;
LocPinY := Height;
width := 45mm;
ENDIF
IF prop.ParentID > 0 THEN
prop.ownDirection := [P]!prop.Direction;
ENDIF
IF prop.ownDirection = 0 THEN
PinX := [P]!PinX+2mm;
PinY := [L]!PinY - [L]!Height-2mm;
ENDIF
IF prop.ownDirection = 1 THEN
PinX := [L]!PinX+[L]!width+2mm;
PinY := [L]!PinY;
ENDIF
IF prop.direction = 0 THEN
Height := [SUMDESC](Height)+ TEXTHEIGHT(TheText,Width) + (prop.numDescendents + 1) * 3mm + 1mm;
Width := [MAXDESC](Width)+ 5mm;
ENDIF
IF prop.direction = 1 THEN
Height := [MAXDESC](Height)+ TEXTHEIGHT(TheText,Width) + 5mm;
Width := [SUMDESC](Width)+prop.numDescendents*5mm;
ENDIF
IF prop.LeftSibling  = 0 THEN
PinX := [P]!PinX + 2 mm;
PinY := [P]!PinY - TEXTHEIGHT([P]!TheText, [P]!Width) - 1 mm;
ENDIF

[J_M]

@Yacine, noted
Thanks for your quick reply!
I will check it and keep you updated

Cheers!