Visio Guy

Visio Discussions => ShapeSheet & Smart Shapes => Topic started by: jimibray on September 08, 2020, 08:29:26 AM

Title: Shape Connection Info
Post by: jimibray on September 08, 2020, 08:29:26 AM
Hi,

I'm trying to identify the shape that a connector line is connected to. I have seen some answers to this question using macro's but I can't get these to work for me, also I only have Visio standard (not pro) so that seems to have some functionality missing. Ultimately I'd like to avoid using a macro if possible.

And... I think this could be possible via the ShapeSheet as I can see that the shape know's what it's connected to in the 1-D Endpoints section - it derives the Begin and End X and Y based on the object it's glued/connected to i.e.

Code
BeginX=PAR(PNT(Sheet.518!Connections.X1,Sheet.518!Connections.Y1))

I tried pulling the sheet.518 out using =mid() but, unsurprisingly, that pulled info from the value and not the formula  :(

So I want to dynamically identify that sheet.xxx object and then pull a property from it that is a more useful string of data - is that possible or do I have to use a macro?

Thanks!
-James
Title: Re: Shape Connection Info
Post by: Paul Herber on September 08, 2020, 08:34:41 AM
Get the formula from
Code
Sheet.518!Connections.X1.Formula
Title: Re: Shape Connection Info
Post by: jimibray on September 08, 2020, 08:54:55 AM
Thanks for the quick reply, Paul.

Because I want this to be dynamic I don't want to reference a specific object (such as sheet.518).

I tried using a user defined cell that contains =BeginX.formula - as BeginX is the field that contains the =par(pnt(sheet.518...) formula.
However, this gives me the "Error in formula" pop up.

Am I missing something with .formula function?

Thanks,
James
Title: Re: Shape Connection Info
Post by: Surrogate on September 08, 2020, 11:49:41 AM
 
Quote from: David J Parker (https://about.me/david_j_parker)'s article Getting the Name of Glued Connection Points (https://blog.bvisual.net/2013/05/21/getting-the-name-of-glued-connection-points/)
A Visio developer asked me if it is possible to get the name of connection points that a connector is glued to in Visio. Well, it is not possible directly from the ShapeSheet, but is easy if you use a little code.
Title: Re: Shape Connection Info
Post by: jimibray on September 09, 2020, 02:49:43 AM
Thanks for sharing that info Surrogate,

I've read that article more than once and think I'm too much of a VBA novice to understand what the code is doing and adapt it to my use - do you have any pointers for Visio VBA 101?

Regarding the part you quoted, my frustration with that is I can see that the shape know what it's glues to and derives it's start point based on the location of sheet.518 - so Visio is already doing something in the background to figure that out and I just want to use that info elsewhere.

I think my frustration stems from how well containers work and I'm able to read properties from the container using =CONTAINERSHEETREF(1)!Prop.xxx and it works so well!

Thanks again for the help so far,
-James
Title: Re: Shape Connection Info
Post by: Surrogate on September 09, 2020, 04:09:58 AM
I think my frustration stems from how well containers work and I'm able to read properties from the container using =CONTAINERSHEETREF(1)!Prop.xxx and it works so well!
Hi, James !
Sorry, i didn't use Containers in Visio. You can get from ShapeSheet (read) properties as value or as formula ?
Title: Re: Shape Connection Info
Post by: jimibray on September 09, 2020, 06:07:10 AM
Hi Surrogate,

I can see the formula in the BeginX cell, but whenever I reference the cell it put's the value and not the formula - is there a Visio equivalent to the Excel function =formulatext?

From your ShapeSheet Knowledge Base I have found the Microsoft training documentation for VBA and Visio so I'm reading that 300 page document to see if I can figure out how to modify the code in the article you previously linked.

Thanks,
-James
Title: Re: Shape Connection Info
Post by: Surrogate on September 09, 2020, 07:22:08 AM
It could be faster:
1. Run macro-recorder.
2. Do the actions you want to automate
3. Analyze the recorder's code
4. finish the code
Title: Re: Shape Connection Info
Post by: jimibray on September 09, 2020, 10:53:02 AM
Hi, I found some code that looked close to what to I want:

Code
Sub GetFlowchartConnections()
' Gets text from shapes connected at each end of every line on page
' Stores text in two shape data fields on each line

    Dim pg As Visio.Page
    Dim shp As Visio.shape
    Dim cnxEndPoints As Visio.Connects
    Dim EP As Visio.Connect
    Dim shpFrom As Visio.shape
    Dim shpTo As Visio.shape
   
    For Each pg In ActiveDocument.Pages
        For Each shp In pg.Shapes
            Call InitFromTo(shp)
           
            ' BeginX only exists if shape is a line
            If shp.CellExists("BeginX", False) Then
                'Get connects collection for current shape
                Set cnxEndPoints = shp.Connects
               
                If cnxEndPoints.Count > 0 Then
                    For i = 1 To cnxEndPoints.Count
                        Set EP = cnxEndPoints(i)
                        If EP.FromPart = visBegin Then
                            ' Get shape this end is attached to
                            Set shpFrom = EP.ToSheet
                            ' Store attached shape's text
                            shp.CellsU("Prop.From").FormulaU = Chr(34) & shpFrom.Text & Chr(34)
                        Else
                            ' Get shape this end is attached to
                            Set shpTo = EP.ToSheet
                            ' Store attached shape's text
                            shp.CellsU("Prop.To").FormulaU = Chr(34) & shpTo.Text & Chr(34)
                        End If
                    Next
                End If
            End If
        Next
    Next

End Sub
Note: I've left out the InitFromTo sub, this is just creating property cells if they don't exist

However, I don't want to write the shape text of the connected shape, I want to pull an existing property from the shape, so I tried modifying the code but it didn't work!
Here's my tweak:
Code
                If cnxEndPoints.Count > 0 Then
                    For i = 1 To cnxEndPoints.Count
                        Set EP = cnxEndPoints(i)
                        If EP.FromPart = visBegin Then
                            ' Get shape this end is attached to
                            Set shpFrom = EP.ToSheet
                            ' Pull Prop into shape
                            shp.CellsU("Prop.From").FormulaU = shpFrom.CellsU("Prop.Conx")
                        Else
                            ' Get shape this end is attached to
                            Set shpTo = EP.ToSheet
                            ' Pull Prop into shape
                            shp.CellsU("Prop.To").FormulaU = shpTo.CellsU("Prop.Conx")

Can someone tell me what I'm doing wrong? - I get the "unexpected end of file" error

Thanks,
James
Title: Re: Shape Connection Info
Post by: Surrogate on September 10, 2020, 02:31:53 AM
Note: I've left out the InitFromTo sub, this is just creating property cells if they don't exist
in which line of this code you get this error ?
i create simple diagram with some connectors, run your code. Get this error, just add properties Prop.To & Prop.From. After this step your code works fine!
UPD first time i use initial code, not your tweak.  Are shapes shpFrom and shpTo have Prop.Conx ?
Title: Re: Shape Connection Info
Post by: jimibray on September 10, 2020, 03:05:40 AM
Hi Surrogate,

Thanks for your help on this, here's the sub I excluded from the last post - for your reference:
Code
Sub InitFromTo(ByRef shape As Visio.shape)
' Create Prop.From/To if they don't exist
' Set both fields to null

    If Not shape.CellExistsU("Prop.From", False) Then
        shape.AddNamedRow visSectionProp, "From", visdefault
    End If
    shape.CellsU("Prop.From").FormulaU = ""
    shape.CellsU("Prop.From.Label").FormulaU = Chr(34) & "From" & Chr(34)
   
    If Not shape.CellExistsU("Prop.To", False) Then
        shape.AddNamedRow visSectionProp, "To", visdefault
    End If
    shape.CellsU("Prop.To").FormulaU = ""
    shape.CellsU("Prop.To.Label").FormulaU = Chr(34) & "To" & Chr(34)

End Sub

The from and to shapes do have a prop.conx I also made user.conx and tried using that but it didn't work.
I tried manually adding "Prop.To", "Prop.From" and "Prop.Conx" to all my shapes but it still doesn't work!
I will try adding this into a fresh visio file and see if that makes it work.

Thanks,
James
Title: Re: Shape Connection Info
Post by: Surrogate on September 10, 2020, 03:18:57 AM
Which information you placed in Prop.Conx ?
Title: Re: Shape Connection Info
Post by: jimibray on September 10, 2020, 03:36:38 AM
Prop.Conx contains a formula referencing two other cells, I've tried to attach this (I'm not sure if my security settings will let me upload the image!)

Essentially I have a shape called terminal that gets placed into a container called connector and the Prop.Conx field concatenates the pin number (that the user enters on shape placement) and the connector ID that it inherits from being placed in the container to generate a string such as J1-1 or J1-2 etc.

I tried making prop.test with a value of "TEST" and referencing that in the macro, the macro is running (i.e. no error) but the prop.to and prop.from fields it creates always just say 0.
[edit] The prop.test field was to see if the 0's i was getting using prop.conx were due to the formula - this doesn't seem to be the issue [/edit]
Title: Re: Shape Connection Info
Post by: Surrogate on September 10, 2020, 03:58:46 AM
Just change this line
Code
shp.CellsU("Prop.From").FormulaU = shpFrom.CellsU("Prop.Conx").FormulaU
For get formula from Prop.Conx cell!
Title: Re: Shape Connection Info
Post by: jimibray on September 10, 2020, 04:40:01 AM
Hi Surrogate,

Firstly thanks for all your help so far - this must be painful guiding me through step-by-step!!

It's very close to work but I get the #NAME? Error when I run the macro with:
Code
shp.CellsU("Prop.From").FormulaU = shpFrom.CellsU("Prop.Conx").FormulaU

I think the formula in the property might be the issue because when I use my Prop.Test which holds the string "test" the macro runs, and it writes TEST into the prop.from and prop.to properties.

-James
Title: Re: Shape Connection Info
Post by: Surrogate on September 10, 2020, 04:58:34 AM
James, can you attach there your document ?
If yes, delete all confidential and meaningfull information ! Just few shapes and connectors between them
Title: Re: Shape Connection Info
Post by: jimibray on September 10, 2020, 05:10:36 AM
Hi Surrogate,

Hopefully this attachment uploads  :)
Title: Re: Shape Connection Info
Post by: Surrogate on September 10, 2020, 05:32:17 AM
in your example cell have formula
Code
Prop.Conn&"-"&Prop.Ref_Des
, which contain concatenation
when your macro try get it formula and set to other shape's cell it convert to string (not formula).

in cell Prop.ConX always use formula "Prop.Conn&"-"&Prop.Ref_Des" ?
Title: Re: Shape Connection Info
Post by: jimibray on September 10, 2020, 05:49:58 AM
Prop.Conx is always this formula
Code
=Prop.Conn&"-"&Prop.Ref_Des

In the wire shape it needs to reference Prop.Conx of the shapes it's connected to in the Prop.To and Prop.From cells.

Ultimately I'd like to run a shape report on the wires and generate a wire list
Wire IDFromTo
Prop.Ref_DesProp.FromProp.To



Title: Re: Shape Connection Info
Post by: Surrogate on September 10, 2020, 08:52:02 AM
IMHO You can get (read) property as string from cell User.Conx ! Where is these sub-strings are concatenated.
Code
                        If EP.FromPart = visBegin Then
                            ' Get shape this end is attached to
                            Set shpFrom = EP.ToSheet
                            ' Pull Prop into shape
                            shp.CellsU("Prop.From").FormulaU = shpFrom.CellsU("User.Conx").ResultStr("")
                        Else
                            ' Get shape this end is attached to
                            Set shpTo = EP.ToSheet
                            ' Pull Prop into shape
                            shp.CellsU("Prop.To").FormulaU = shpTo.CellsU("User.Conx").ResultStr("")
                        End If
                    Next
But if you change some connections, values in Prop.To/Prop.From don't updated.
Title: Re: Shape Connection Info
Post by: Surrogate on September 10, 2020, 09:24:45 AM
Code
                    For i = 1 To cnxEndPoints.Count
                        Set EP = cnxEndPoints(i)
                        If EP.FromPart = visBegin Then
                            ' Get shape this end is attached to
                            Set shpFrom = EP.ToSheet
                            ' Pull Prop into shape
                             ' in next line of code you define shpFrom and create reference to prop.conx property of this shape !
                            shp.CellsU("Prop.From").FormulaU = "sheet." & shpFrom.ID & "!prop.Conx"
                        Else
                            ' Get shape this end is attached to
                            Set shpTo = EP.ToSheet
                            ' Pull Prop into shape
                            ' in next line you define  shpTo and create reference to prop.conx property of this shape !
                            shp.CellsU("Prop.To").FormulaU = "sheet." & shpTo.ID & "!prop.Conx"
                        End If
                    Next
Title: Re: Shape Connection Info
Post by: jimibray on September 10, 2020, 09:37:34 AM
Hi Surrogate,

The second block of code you posted is working perfectly :) if I change the connectivity in the diagram and re-run the macro it updates the connectivity!

Thanks so much for helping, you're a hero!!!

-James
Title: Re: Shape Connection Info
Post by: Surrogate on September 11, 2020, 02:23:00 AM
James, thank you !
But i rarely use automation with connectors, in Russian forum our member share own repository
https://github.com/gtfox/SAPR_ASU (https://translate.google.com/translate?sl=ru&tl=en&u=https%3A%2F%2Fgithub.com%2Fgtfox%2FSAPR_ASU)
With great automation like this
(http://forumimage.ru/uploads/20200817/159765446481482184.gif)