Shape Connection Info

Started by jimibray, September 08, 2020, 01:29:26 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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.

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

Sheet.518!Connections.X1.Formula
Electronic and Electrical engineering, business and software stencils for Visio -

https://www.paulherber.co.uk/

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

#3
 
Quote from: David J Parker's article Getting the Name of Glued Connection PointsA 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

#5
Quote from: jimibray on September 09, 2020, 07:49:43 AMI 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

#7
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:


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:

                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

#9
Quote from: jimibray on September 09, 2020, 03:53:02 PM
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:

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

#12
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

#13
Just change this line
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:
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