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
Get the formula from
Sheet.518!Connections.X1.Formula
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
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.
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
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 ?
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
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
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
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 ?
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
Which information you placed in Prop.Conx ?
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]
Just change this line
shp.CellsU("Prop.From").FormulaU = shpFrom.CellsU("Prop.Conx").FormulaU
For get formula from Prop.Conx cell!
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
James, can you attach there your document ?
If yes, delete all confidential and meaningfull information ! Just few shapes and connectors between them
Hi Surrogate,
Hopefully this attachment uploads :)
in your example cell have formula 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" ?
Prop.Conx is always this formula
=Prop.Conn&"-"&Prop.Ref_DesIn 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 ID | From | To |
| Prop.Ref_Des | Prop.From | Prop.To |
IMHO You can get (read) property as string from cell User.Conx ! Where is these sub-strings are concatenated.
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.
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
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
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)