Shape Connection Info

[jimibray]

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

[Paul Herber]

Get the formula from

Code

Sheet.518!Connections.X1.Formula

[jimibray]

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

[Surrogate]

 

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.

[jimibray]

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

[Surrogate]

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 ?

[jimibray]

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

[Surrogate]

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

[jimibray]

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

[Surrogate]

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 ?

[jimibray]

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

[Surrogate]

Which information you placed in Prop.Conx ?

[jimibray]

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]

[Surrogate]

Just change this line

Code

shp.CellsU("Prop.From").FormulaU = shpFrom.CellsU("Prop.Conx").FormulaU

For get formula from Prop.Conx cell!

[jimibray]

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