Listing Connection Objects on a Page

Started by wapperdude, August 10, 2014, 11:33:31 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

wapperdude

Well, wasn't planning on doing a series about finding, showing, listing connection objects and I don't think 2 posts qualifies.

So, this post is about searching a page for all of its connection objects.  A connection object is anything glued to something else.  It may be a dynamic connector, a simple 1D shape, or even a 2D shape.

The code goes thru and examines all of the connections on a page.  It then determines how connected and to which shape.  Also, it distinguishes between walking connections and connection to connection points.  It tell you which row even.

The results are sent to an Excel worksheet.  The worksheet is automatically formatted for viewing (my) pleasure.  There is an example Visio file attached.

Couple of notes about the code. 
  1.  1/4 of it is the connection analysis.  3/4 of it is writing to Excel and doing all of the formatting.  A lot of formatting.  Mostly formatting.
  2.  I wrote it...wait...is that Yacine yelling at me?   :)...as one single sub-routine.  But, the flow is very straight forward, lots of comments, and partitioned so that it could be easily broken into smaller modules.
  3.  There is no error checking in the code.   ::)  Hey, it's a demo! 
  4.  Because of how the file is "partitioned", the Excel stuff could be removed, and results displayed as Userform listbox.  But, formatting the listbox was beyond me.

Anyway, for those who are always searching how to do the connectivity stuff, this might provide useful.

Wapperdude

...and yes, I still don't like to do programming.
Visio 2019 Pro

Yacine

1)  ;D
2) I needed to initialise toShp as Shape and change the reference to another excel version to get it working. Otherwise: nice job!
Yacine


wapperdude

Thanks Yacine, Jumpy!   8)

Not sure what happened to the  "toshp" declaration, I re-added it to the definitions.  I know it was there at one time, must have inadvertently deleted it.  Strange thing, even with it missing, the code runs fine on my machine.  But, its omission is bad form.  Thanks for catching that.

Not sure why the reference to excel needed modification.  I've updated the way Excel is started.  It's claimed that this is a universal invocation.  Let me know if you get a chance.

Wapperdude
Visio 2019 Pro

wapperdude

Oops!  Forgot to change closing Excel app at end of code.  Corrected.

But, don't know if it's my PC or what, but, now if I run macro a 2nd time, the code doesn't seem to set myExSheet to the identified worksheet.  Code then bombs because of an error.  It's OK 1st time thru, but there's this glitch the 2nd time. 

Would appreciate it others can confirm this behavior, and even more if there's a known solution!

Wapperdude
Visio 2019 Pro

wapperdude

Well coding fans, I'm happy to say that the macro failure to run on repeated executions was NOT a code error.   :o   ;D   8)  After some digging, and creative googling, I came across this:   ???

http://support.microsoft.com/kb/178510
To automate Microsoft Excel, you establish an object variable that usually refers to the Excel Application object or the Excel Workbook object. Other object variables can then be set to refer to a Worksheet, a Range, or other objects in the Microsoft Excel object model. When you write code to use an Excel object, method, or property, you should always precede the call with the appropriate object variable. If you do not, Visual Basic establishes its own reference to Excel. This reference might cause problems when you try to run the automation code multiple times. Note that even if the line of code begins with the object variable, a call may be made to an Excel object, method, or property in the middle of the line of code that is not preceded with an object variable.
   :o

Scary, I actually understood this jibberish.   ::)
Bottom line, using the standard practice With .... End With so that you can avoid a lot of typing, will produce the problem.   :P  All implied references have to be literal!!!  Thank you Microsoft.   Aaaargh!

Well, here's an updated version that will allow multiple repeated executions without bombing. 

If you look at the code for the Excel Formatting Section, will see the slew of repeated, literal reference entries.

Enjoy!
Wapperdude

...and people wonder why I hate programming!  Really?!?   >:(
Visio 2019 Pro

lrdgrd

Very nice wapperdude!

I was doing something similar, but with some extra info on it, but im still struggling to get it.  Is there a way to know from which side the connector is going out/to from the shapes?

The idea is once i manage to know how to "read" the side the connections are coming from, the next step would be to start from an excel file and create all the shapes and connections automatically from there.
lrdgrd (LRDGRD)
One of Seven!

wapperdude

Yes, you can identify each end of the connector.  In fact the code does that.  When you look at the Excel table, if both ends of a connector are glued to something, then, there is a double entry, one for each end.  The 2nd column indicates which end of the connector is being referenced.

I'm about to leave to fetch daughter and husband back from honeymoon, so, can't show precise entries in the code.  If you step thru, using "f8", then as each line executes, you put cursor over an entry and see its value.  If still questions, it'll probably be tomorrow before I can get back.

Wapperdude
Visio 2019 Pro

lrdgrd

Yes, i see that it is saying from BeginX and EndX for the connector itself, but for the shape i cant seem to find it (2D shape, the one where the connector is attached to).  Or im not understand the way it works.
lrdgrd (LRDGRD)
One of Seven!

wapperdude

Oh.  Think I misinterpreted what you wanted...and still a little uncertain, actually. 

Point of clarification, BeginX and EndX apply only to 1D shapes.  For 2D shapes, the gluing happens either by specific connection point or walking connection.  In the case where a 1D shape is glued to a 2D shape, that info is shown in the 2nd and 3rd columns.  In the code, that the section dealing with "toShp" and "toPartID".

So, the code looks at each connection object (conObj) in the pages connections collection.  A conObj is more like a glue point, not actual shapes.  A 1D shape may have 2 conObj's, the begin and end points.  The analysis steps thru each glue point, first is the "from" aspect of the conObj.  These are shapes that are glued to something.  That is, typically 1D shapes.  Then, the code moves to the "to" aspect.  These are the objects that have been glued to.  Thus, a conObj has both "from" shape and "to" shape. 

If you look at this topic, http://visguy.com/vgforum/index.php?topic=5844.0, the code is better documented.  The 1st section of the code deals with the highlighting, as per your post, but the 2nd section deals with what we're discussing here.  The reporting has been improved to give a better identification of the connection point of the 2D "toShp".

Hope this answers your question, unless of course, you meant identify actual "side" of the 2D shape, e.g., left or top.  In which case, the code doesn't do that at all.  I'd have to go back and see if that's even part of the connects object family.

Wapperdude
Visio 2019 Pro

Jumpy

Quote from: wapperdude on August 14, 2014, 03:14:44 PM
Hope this answers your question, unless of course, you meant identify actual "side" of the 2D shape, e.g., left or top.  In which case, the code doesn't do that at all.  I'd have to go back and see if that's even part of the connects object family.

To save you some time, it's not. If you connect to connection points and not use walking glue, you can name your connection points accordingly, so when a ToCell from a connect onject is sth. like Connections.LeftSide.X you know it's the left side.

Without that knowledge you have to analyse the path (geometry) of the connector (first and second row of geometry section or the before last and last ones).

wapperdude

#11
Duh!   ::)  Good point Jumpy!!!   :)  The code does look for named connectors.  In fact, the demo files have been using them, just didn't occur to use names as you suggest!

...and with this opportunity, I've updated the code:
  1.)  to use called sub-routine...everyone cheer!    ;D
  2.)  even better code commenting.

The attached file exports to Excel, but, comment out the call to Excel, and uncomment the show listbox, and only listbox comes up.  Or, leave both commented out.
Also, note, this only identify connections to a selected shape.  Next post does entire page.

Enjoy!
Wapperdude
Visio 2019 Pro

wapperdude

Previous updates had digressed to only working with just a selected shape, not the entire page.

Here's the entire page connections version.  Includes the sub-routine calls.

Enjoy
Wapperdude
Visio 2019 Pro

benjamin.w.thomas

This is amazing!  How would you tweak the code to show the user text for the object instead of the object name?  My head hurts from trying.

wapperdude

#14
The code is tweaked.  I added comments to try and show the changes.  I assume that by user text, you merely mean shape text that's been entered directly by the user, and I assume you're referring to the connector shapes.  Visio accesses shape text via characters objects, not by some "text" object.  After that, it's fairly straight forward.

The updated code has usual Dim declarations.  Then, there are two lines of code to fetch the shape text.  The connector shape in question is the "fromShp" in the code.  The remaining code changes are merely substituting "fromShp" with "shpText", which holds the text string.

Note, in my text diagram, each connections object has a field inserted text.  So, those entries are also found, they just happen to be the shape name.

Enjoy!
Wapperdude
Visio 2019 Pro