Find one shape's ID by using formula in a different shape

Started by natelfo, May 28, 2018, 11:39:18 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

natelfo

Let's say I have a box with a connection point, which has a line glued to it. The box's color is dependent on the line color, so if the line is red, the box is red.  I need to make multiple copies of this setup in different projects, so what I want to do is have a formula in the box's shape sheet that will find the shape ID of the line connected to it. 
The line's 1-D Endpoints have the formula:
PAR(PNT(Sheet.1!Connections.1.X,Sheet.1!Connections.1.Y))
Is there any way to have a formula in the box's sheet that will find the shape ID of whatever shape has that specific formula in it's EndY cell?

Surrogate

Quote from: David J Parker in 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.
You can find code in that article

Visioistgeil

Quote from: Surrogate on May 29, 2018, 01:45:51 AM
Quote from: David J Parker in 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.
You can find code in that article

Hi, I'm a beginner in Visio and therefore this isn't a little code for me ;D Is there any easier way to replace '836' at BeginX by the value of prop.source? I want to create arrows (with data of an excel sheet) which connect automatically with the right boxes if they are droped on the page.

Thanks in advanced!

wapperdude

There's no shapesheet mechanism to do that.  It has to be done with code.

Wapperdude
Visio 2019 Pro

Visioistgeil

Quote from: wapperdude on August 13, 2018, 01:29:37 PM
There's no shapesheet mechanism to do that.  It has to be done with code.

Wapperdude

Thank you, but is it possible to drop all arrows on the page and after that run the code which uses the prop.sourceM to connect the arrows to the boxes?

wapperdude

#5
Yes, that is a possible scenario.  It takes some preliminary work and organization.
1)  Each shape that you drop must have a way for the code to identify and associate with Excel entry
2)  Excel file needs to have two entries, one for the "source" shape and one for the "destination" shape
3) decide how each end of connector glues to respective shape, i.e., to a specific connection point  or by "walking" glue.

Assumptions are then:
1) source and destination shapes are on the page
2) shapes can be added or moved, macro re-run
3) the code will add connectors as necessary
4) the code will initially format the connectors, e.g., line weight, arrowheads, etc.
5) the Excel file is readily accessible by the code
6) code will remove "floating" connectors; maybe highlight connectors with single attached end.


Approach:
Code runs thru each row of Excel
      a) grabs source shape identifier
      b) loops thru all shapes on page to find a match
      c) if match found
             i) creates connector -- formats connector as desired
             ii) glues end
            iii) moves to destination identifier, loops thru the shapes again, glues other end
      d) if no matching shape found, moves to next Excel row 


Wapperdude


Visio 2019 Pro

Visioistgeil

Quote from: wapperdude on August 13, 2018, 03:39:07 PM
Yes, that is a possible scenario.  It takes some preliminary work and organization.
1)  Each shape that you drop must have a way for the code to identify and associate with Excel entry
2)  Excel file needs to have two entries, one for the "source" shape and one for the "destination" shape
3) decide how each end of connector glues to respective shape, i.e., to a specific connection point  or by "walking" glue.

Assumptions are then:
1) source and destination shapes are on the page
2) shapes can be added or moved, macro re-run
3) the code will add connectors as necessary
4) the code will initially format the connectors, e.g., line weight, arrowheads, etc.
5) the Excel file is readily accessible by the code
6) code will remove "floating" connectors; maybe highlight connectors with single attached end.


Approach:
Code runs thru each row of Excel
      a) grabs source shape identifier
      b) loops thru all shapes on page to find a match
      c) if match found
             i) creates connector -- formats connector as desired
             ii) glues end
            iii) moves to destination identifier, loops thru the shapes again, glues other end
      d) if no matching shape found, moves to next Excel row 


Wapperdude

Thank you very much, i will try it  ;D Do you think it is a doable task for a beginner in VBA ?

wapperdude

Doable...yes...depends...

Have you done any programming before?  Is there a deadline?  Are you patient?  Are you determined?

Below is link that references where to begin.  Thing to note, if you don't first succeed, failure doesn't cost you anything but time.  Important rule...always make a backup copy of original file and incremental copies before you make code changes.

Next thing to know, if (ok, when) you get stuck, upload to the forum, someone will provide help.  So, you're not really alone.

http://visguy.com/vgforum/index.php?topic=2173.msg9666#msg9666

Wapperdude
Visio 2019 Pro

Yacine

The code you're looking for is in my upload in this topic: http://visguy.com/vgforum/index.php?topic=7994.0
It's a routine ("ConnectIt") I found in Junichi's shapes. It does look for another property than the colour, but I'm sure you can figure that out.
HTH,
Y.
Yacine

Visioistgeil

Thanks for your help!

I already developed a "static" code and want to get it more dynamically.

Dim Arrow As Visio.Shape
Dim Source As Shape

Set Source = Application.ActiveWindow.Page.Shapes("Name")

The Arrow is linked to an excel list which contains the name of the source. Is it possible to replace the name through a variable for example i and set i to the given source in the excel list?

My idea is something like:

Dim Arrow As Visio.Shape
Dim Source As Shape
Dim i as String


"Arrow.source = i 'i can't find a expression for it
Set Source = Application.ActiveWindow.Page.Shapes("i") 'i think it isn't possible to set the name as a variable and i have to use another expression

Sorry for my ignorance :(

wapperdude

#10
Using named shapes is the least desirable method of handling shapes.  Under some circumstances, Visio will append a number to the name.  That creates problem.  The best method, instead of renaming the shape to make it unique, is to add an "identifier" to its shapesheet.  For example, instead of sheet.1 getting renamed to XYZ, add a User Defined entry to the shapesheet, perhaps User.shpNm = "XYZ".  Yes, with the quotes.  Then the code will loop thru the shapes, and check to see if User.shpNm exists, and if it does, then, does its value match "XYZ"?  If so, that's the shape you want.  A tad extra work, but very reliable.

On another note, I mentioned, various types of gluing methods.  The method you want determines how the gluing syntax.  The code below has all three methods as means of example.  The comments should help clarify the method used.

In your case, each shape, in my example, ID = 1, 2, 3, 4, are the shapes to be connected to each other.  For your application, these shapes would be replaced by the results of the program identifying the correct shape matching the Excel definition.  The ID = 5, 6, 7 are the connectors.


Sub CnxDropMethods()
    Dim vsoCell1 As Visio.Cell
    Dim vsoCell2 As Visio.Cell
    Dim vsoShp As Shape
   
'Method1: From CnPt to CnPt
    Set vsoCnx = ActiveDocument.Masters.ItemU("Dynamic connector")
    Set vsoShp = ActivePage.Drop(vsoCnx, 0#, 0#)
    'Next is optional:  set connector to straight.  Could add to the other methods.
    vsoShp.CellsSRC(visSectionObject, visRowShapeLayout, visSLOLineRouteExt).FormulaU = 1
    vsoShp.CellsSRC(visSectionObject, visRowShapeLayout, visSLORouteStyle).FormulaU = 16
   
    'Connector start point
    Set vsoCell1 = vsoShp.CellsU("BeginX")
    Set vsoCell2 = ActiveWindow.Page.Shapes.ItemFromID(1).CellsSRC(7, 0, 0)
    vsoCell1.GlueTo vsoCell2
   
    'Connector end point
    Set vsoCell1 = vsoShp.CellsU("EndX")
    Set vsoCell2 = ActiveWindow.Page.Shapes.ItemFromID(2).CellsSRC(7, 0, 0)
    vsoCell1.GlueTo vsoCell2
   
'Method2:  From CnPt to WalkPt
    Dim vsoCell3 As Visio.Cell
    Dim vsoCell4 As Visio.Cell
   
    Set vsoCnx = ActiveDocument.Masters.ItemU("Dynamic connector")
    Set vsoShp = ActivePage.Drop(vsoCnx, 0#, 0#)
    Set vsoCell3 = vsoShp.CellsU("BeginX")
    Set vsoCell4 = ActiveWindow.Page.Shapes.ItemFromID(1).CellsSRC(7, 0, 0)
    vsoCell3.GlueTo vsoCell4
    Set vsoCell3 = vsoShp.CellsU("EndX")
    Set vsoCell4 = ActiveWindow.Page.Shapes.ItemFromID(3).CellsSRC(1, 1, 0)
    vsoCell3.GlueTo vsoCell4

'Method3: From WalkPt to WalkPt
    Dim vsoCell5 As Visio.Cell
    Dim vsoCell6 As Visio.Cell
   
    Set vsoCnx = ActiveDocument.Masters.ItemU("Dynamic connector")
    Set vsoShp = ActivePage.Drop(vsoCnx, 0#, 0#)
    Set vsoCell5 = vsoShp.CellsU("BeginX")
    Set vsoCell6 = ActiveWindow.Page.Shapes.ItemFromID(1).CellsSRC(1, 1, 0)
    vsoCell5.GlueTo vsoCell6
    Set vsoCell5 = vsoShp.CellsU("EndX")
    Set vsoCell6 = ActiveWindow.Page.Shapes.ItemFromID(4).CellsSRC(1, 1, 0)
    vsoCell5.GlueTo vsoCell6

End Sub


Wapperdude
Visio 2019 Pro

wapperdude

#11
Well, here is example of an approach.  There is a main subroutine, CnctShps, that loops thru all shapes on a page.  It checks for a User.shpNm cell as a method of finding a valid shape.  It then checks the contents of the cell and compares with either the Excel source or destination names.  Successful comparison results in assignment to either of two shapes.  These are then used as inputs to a 2nd routine that drops and connects these shapes with a dynamic connector using the walking glue method (Method3 from before)

Some issues:  need some error handling before calling 2nd routine in case there are no matching shapes, or only one matching shape, as the 2nd routine expects two shapes.  Still need code to go thru the Excel file and step thru the rows and finds the appropriate source and destination names.  Finally, each previously named shape needs to have the User.shpNm cell added to it.

Code fore main loop:

Sub CnctShps()
    Dim vShp1 As Shape
    Dim vShp2 As Shape
    Dim iShp As Shape
    Dim i As Integer
    Dim shpNM As String
    Dim exclSrc As String
    Dim exclDes As String
   
    For Each iShp In ActivePage.Shapes
        If iShp.CellExists("User.shpNm", visExistsAnywhere) = True Then
'            Debug.Print iShp.CellsU("User.shpNm").ResultStr("")
            shpNM = iShp.CellsU("User.shpNm").ResultStr("")
            exclSrc = "shpA"        'This is where you would get source name from Excel
            exclDes = "shpD"        'This is where you would get destination name from Excel
            If shpNM = exclSrc Then
'                Debug.Print "Found Source"
                Set vShp1 = iShp
            End If
            If shpNM = exclDes Then
'                Debug.Print "Found Destination"
                Set vShp2 = iShp
            End If
        End If
    Next
    Call DrpCnM3(vShp1, vShp2)
End Sub


And the code for the connector method3 routine:

Sub DrpCnM3(vShp1 As Shape, vShp2 As Shape)
'Method3: From WalkPt to WalkPt
    Dim vsoCell5 As Visio.Cell
    Dim vsoCell6 As Visio.Cell
    Dim vsoShp As Shape
   
    Set vsoCnx = ActiveDocument.Masters.ItemU("Dynamic connector")
    Set vsoShp = ActivePage.Drop(vsoCnx, 0#, 0#)
    Set vsoCell5 = vsoShp.CellsU("BeginX")
    Set vsoCell6 = vShp1.CellsSRC(1, 1, 0)
    vsoCell5.GlueTo vsoCell6
    Set vsoCell5 = vsoShp.CellsU("EndX")
    Set vsoCell6 = vShp2.CellsSRC(1, 1, 0)
    vsoCell5.GlueTo vsoCell6
End Sub

Wapperdude

Visio 2019 Pro

Visioistgeil