none
Gathering names from graph of shapes ... change in syntaks ??? RRS feed

  • Question

  • I have this code from one of my very old Excel files concerning family trees - it's supposed to get names from descendents in a recursive way.

    I now need it to just SELECT the descendent shapes, but the code can't even compile in Excel 365 Home - the problem is "thisshape.connectors.out" maybe more.

    Sub selectFurther(thisshape As Shape)
        '
        ' RECURSIVE code is used in this sub
        '
        Dim con As Shape
        Dim descendentShape As Shape
        namecount = namecount + 1
        MyNames(namecount) = thisshape.Name
        For Each con In thisshape.connectors.out
            namecount = namecount + 1
            MyNames(namecount) = con.Name
            Set descendentShape = con.ConnectorFormat.EndConnectedShape
            selectFurther descendentShape
        Next con
    End Sub

    What has changed from "back then" (whenever it was) til Excel 365 Home version concerning connectors and the connecting sites ?

    Thursday, February 15, 2018 10:52 AM

Answers

  • Oh, I think it was running back then ... but it's 20-25 years back, maybe more - I simply can't remember now.

    Trust me, it didn't work back then:)

    If you've got 70-80 nodes at a rough guess you'll have about 200 shapes including connectors and maybe other non-connector type lines depending on how you've got things arranged. It really shouldn't take long to sort that lot out into some sort of table with the information you need (parent child relationships) to build a 'virtual' tree you can work with. 


    Thursday, February 15, 2018 10:19 PM
    Moderator

All replies

  • I have this code from one of my very old Excel files concerning family trees - it's supposed to get names from descendents in a recursive way.

    I now need it to just SELECT the descendent shapes, but the code can't even compile in Excel 365 Home - the problem is "thisshape.connectors.out" maybe more.

    Sub selectFurther(thisshape As Shape)
        '
        ' RECURSIVE code is used in this sub
        '
        Dim con As Shape
        Dim descendentShape As Shape
        namecount = namecount + 1
        MyNames(namecount) = thisshape.Name
        For Each con In thisshape.connectors.out
            namecount = namecount + 1
            MyNames(namecount) = con.Name
            Set descendentShape = con.ConnectorFormat.EndConnectedShape
            selectFurther descendentShape
        Next con
    End Sub

    What has changed from "back then" (whenever it was) til Excel 365 Home version concerning connectors and the connecting sites ?

    Thursday, February 15, 2018 10:51 AM
  • Your code would not have compiled in any Excel version. Whilst it might exist in an 'old file' I doubt it ever worked!

    If I follow you want to find all connectors connected to a shape, get the shape connected to the other end of the connector, and same again with each connected shape. Unfortunately the Shape object (unless it's a connector line - see later) does not return a list or collection of connectors connected it, so your approach will not work.

    What you could do is loop all shape, if a connector
       If shp.Connector then
            If shp.ConnectorFormat.BeginConnected then
                set shpBegin = shp.ConnectorFormat.BeginConnectedShape
    and similar with 'End' 

    With just that there are several approaches you could devise to get a representation of your tree. Though it would be much easier of course to work with a real treeview!

                

    Thursday, February 15, 2018 12:20 PM
    Moderator
  • Oh, I think it was running back then ... but it's 20-25 years back, maybe more - I simply can't remember now.

    I have tried to implement it in some SmartArt chart too - but oh, it's way too slow when you have >70-80 nodes !

    Too my family tree is a "real" one - not these amputed theoretical ones ... loose ends here, there and everywhere - both father and mother is there too - I haven't fund anything that can manage that - but never mind, I think I have made up some ideas for code that can do what I want.

    I'll have to dive into your code to see whats going on and change it to fit my needs ... thx

    Thursday, February 15, 2018 7:22 PM
  • Oh, I think it was running back then ... but it's 20-25 years back, maybe more - I simply can't remember now.

    Trust me, it didn't work back then:)

    If you've got 70-80 nodes at a rough guess you'll have about 200 shapes including connectors and maybe other non-connector type lines depending on how you've got things arranged. It really shouldn't take long to sort that lot out into some sort of table with the information you need (parent child relationships) to build a 'virtual' tree you can work with. 


    Thursday, February 15, 2018 10:19 PM
    Moderator
  • I know for a fact that it's too slow because I started out with using SmartArt but it slowed down so much I couldn't use it.

    So now I try myself with a little help from here and there ;-))

    Friday, February 16, 2018 7:15 AM
  • Hello Ksor2,

    What did you want to do by the code? What do you mean descendent shapes? What's the version of your current Office?

    I checked Excel Interop API for recently version and did not find a property named connectors for a shape object. I would suggest you detail your requirement for a workaround.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 19, 2018 11:32 AM
  • Hello,

    I found something similar: http://www.tek-tips.com/viewthread.cfm?qid=1673743

    in the post they say thisshape.connectors.out does not exists. It looks like your code has been copied from there?

    the fact "connectors" and "out" have no capital letters, means it never compiled.

    Maybe that post can help you further?

    Monday, February 19, 2018 12:06 PM
  • Yes, I'm more and more convinced that it some sort of psoudo code I has found !

    But what I want it to do is just gather NAMES of the descentdent shapes -a person is represented in my family tree by a rectangle-shape with a name of the ID of that person.

    I want to be able to select a connection (an directed arrow) and the code should then SELECT all shapes from that arrow downwards in the tree - it's divided inte two parts 1) gathereing the names and 2) selecting them.

    The 2) is no problem - I have that - I nearly have 1) too - I have found some code that does something like what I want - I just have to dive inte it, so I understand it and can cut what I need from there.

    Monday, February 19, 2018 12:18 PM
  • Hello Ksor2,

    >>But what I want it to do is just gather NAMES of the descentdent shapes

    I still fail to know what's the descentdent shapes and I would suggest you share a simply Excel file so we could try to use it to reproduce your issue.

    You could share the file via Cloud Storage, such as One Drive, and then put link address here.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 19, 2018 12:26 PM
  • Terry, the OP also (multi) posted the same question in the VBA group. He marked it as answered but seems he forgot to advise this group.

    Monday, February 19, 2018 3:56 PM
    Moderator