Export (Report) shapes and connectivity

[Jim]

I'm trying to export a report (into Excel) that is a representation of shapes and their connectivity.   The Orgchart report is close because it adds the "Unique_ID" column and the "Reports_To" column.   However, I'd like to generate a report like this, but not being limited to the org chart stencil (which restricts having 2 "managers" for 1 "subordanite."  Basically, I'm trying to create a generic report that shows the connectivity relationships between shapes. 

I'm close to "breaking down" and writing a VBA to do what I want by interpreting the X and Y positions of blocks and connectors...but it seems like a report like this would already exists.

Any ideas as to where I should start?

[bhcohen]

I was able to do this kind of thing with a combination of the "Export to Database" function and the "Report" function, though ultimately it required me to write a little program to process the exported data.

The output from the Export to Database went to an Access database.  For Dynamic Connectors, the BeginX and EndX columns contained the name of the shapes connected to the dynamic connector.  I needed the output from the Report function to associate the shape name with a shape id. 

I did not have to look at the X, Y coordinates



-Bruce

[Jim]

bhcohen....Very clever! So you actually parse the data in the BeginX column and extract the Shape Name data?

example data from BeginX field...

BeginX=PAR(PNT(Triangle!Connections.X4,Triangle!Connections.Y4))

where "Triange" is the Shape Name.

But then to find what Shape ID the Shape Name is assocated with (in this case "Triange") you need to run a Report that puts out both the Shape ID and the Shape Name.


I can see how you would work with this...I'm amazed that you actually discovered this.

I'm wondering if anyone knows how to add the "Shape Name" to the things that can be exported via the "Export to Database" function?  This would save us having to do this cross file lookup described above.

[Aleem]

I was able to do this kind of thing with a combination of the "Export to Database" function and the "Report" function, though ultimately it required me to write a little program to process the exported data.

The output from the Export to Database went to an Access database.  For Dynamic Connectors, the BeginX and EndX columns contained the name of the shapes connected to the dynamic connector.  I needed the output from the Report function to associate the shape name with a shape id. 

I did not have to look at the X, Y coordinates



-Bruce

Hi bhcohen, I did the same thing that you described here but for a process flow diagram. I needed to find out which pipeline was connected to which process unit/tank/node and the beginX and endX works but I'm having another problem. I have attached instrumentation shapes to each pipeline and I need to find out which shape is attached to which pipeline as well but there is no beginx or endx for this. I was hoping that the pipeline report would have told me this but it doesn't, do you know of any way to get this information out? e.g. in the example PFD how can I know that pipeline P-1 is connected to I-2?

http://ifile.it/umnjxp4 

[aledlund]

you can do some of this with vba code
al

first the code to chase the page connections. (added examples for pfd solution)

Code Select Expand



'
' added a simple test for whether the shape is other than oneD
' since testing the oneD flag is often not sufficient
'
Public Function whatkindofShape _
       (ByVal vsoShape As Visio.Shape, _
       Optional ByVal strSolution As String = "network") _
       As String

   On Error GoTo whatkindofShape_err

   Dim strReturn As String
   strReturn = "unknown"
   
   Dim cellClass As Visio.Cell
   Dim strClass As String
   strClass = ""
   
   ' does it have subshapes
   Dim visShapes As Visio.Shapes
   Set visShapes = vsoShape.Shapes
   Dim intShapes As Integer
   intShapes = visShapes.Count
   
   ' how many geometry sections

   Dim intGeometrySections As Integer
   intGeometrySections = vsoShape.GeometryCount
   
   ' give some generic flexibility
   Select Case vsoShape.Type
           
       ' is it a group or an image
       Case visTypeMetafile, visTypeBitmap
           ' used in network topology, but keep it
           strReturn = "vertex"
           GoTo whatkindofShapeReturn
           
       ' something to be ignored
       Case visTypeGuide, visTypeIsControl, visTypeForeignObject
           strReturn = "ignore"
           GoTo whatkindofShapeReturn
   
       Case visTypeGroup
           If strSolution = "network" Then
               strReturn = "vertex"
               GoTo whatkindofShapeReturn
           End If
   
   End Select
   
   ' is it flagged as oneD
   If vsoShape.OneD <> 0 Then
       
       ' first test for a dynamic connector, have to check
       ' the master because the name may be changed
       If Left(LCase(vsoShape.Master.NameU), 17) = "dynamic connector" Then
           strReturn = "edge"
           GoTo whatkindofShapeReturn
       End If
       
       If vsoShape.ObjectType = visLOFlagsRoutable Then
           strReturn = "edge"
           GoTo whatkindofShapeReturn
       End If
       
       ' is it something fancy ? then pretend that it is an edge
       ' shape was tested for group above
       If strSolution = "network" Then
           If 1 < intGeometrySections Then
               strReturn = "edge"
               GoTo whatkindofShapeReturn
           End If
       End If
       
       If strSolution = "pfd" Then
           If vsoShape.CellExists("user.shapeclass", False) = True Then
               Set cellClass = vsoShape.Cells("user.shapeclass")
               strClass = LCase(cellClass.ResultStr(""))
               Select Case strClass
                   Case "pipelines"
                       strReturn = "pipelines"
                       GoTo whatkindofShapeReturn
               End Select
               
           End If
       
       End If
       
       
   Else
       ' it is a twoD shape
       If strSolution = "network" Then
           strReturn = "vertex"
           GoTo whatkindofShapeReturn
       End If
       
       If strSolution = "pfd" Then
           If vsoShape.CellExists("user.shapeclass", False) = True Then
           
               Set cellClass = vsoShape.Cells("user.shapeclass")
               strClass = LCase(cellClass.ResultStr(""))
           
               Select Case strClass
                   Case "equipment"
                       strReturn = "equipment"
                       GoTo whatkindofShapeReturn
                   Case "instrument"
                       strReturn = "instrument"
                        GoTo whatkindofShapeReturn
               End Select
           End If
       
       End If
       
       
   End If
   
   
whatkindofShapeReturn:
   
   If strReturn = "unknown" Then
       Debug.Print vsoShape.Name & " " & intGeometrySections & " " & vsoShape.Type
   End If
   
   whatkindofShape = strReturn
   Exit Function
   
whatkindofShape_err:

   Debug.Print "whatkindofShape " & Err.Description
   whatkindofShape = strReturn
   

End Function


'
' get nodes and edges
'
Public Sub getTwoDandOneD _
       (ByVal vsoPage As Visio.Page, _
       Optional ByVal strSolution As String = "network")

   Dim vsoShapeFrom    As Visio.Shape
   Dim vsoShapeTo      As Visio.Shape
   
   Dim vsoConnects     As Visio.Connects
   Dim vsoConnect      As Visio.Connect
   
   Dim vsoFromData     As Visio.VisFromParts
   Dim vsoToData       As Visio.VisToParts
   
   Dim intFromData     As Integer
   Dim intToData       As Integer
   Dim strFrom         As String
   Dim strTo           As String
   
   Dim strFromType     As String
   Dim strToType       As String
   Dim intFromId       As Integer
   Dim intToId         As Integer
   
   On Error GoTo getTwoDandOneD_Err
   
   ' debug types to handle immediate windown
   Dim blnList As Boolean
   blnList = True
   Dim blnType As Boolean
   blnType = False

  ' Get the Connects collection for the page.
   Set vsoConnects = vsoPage.Connects

   ' Loop through the Connects collection.
   For Each vsoConnect In vsoConnects
   
       strFromType = ""
       strToType = ""
       intFromId = -1
       intToId = -1
       
       ' Get the From information.
       ' Use intFromData to determine the type of connection.
       Set vsoShapeFrom = vsoConnect.FromSheet
       
       strFromType = whatkindofShape(vsoShapeFrom, strSolution)
       intFromId = vsoShapeFrom.ID
       
       intFromData = vsoConnect.FromPart
       If (intFromData < VisFromParts.visControlPoint) Then
           
           strFrom = vsoConnect.FromCell.Name
                 
       Else
           ' Converts the VisFromParts value to a string.
           ' Control points are numbered starting with 1
           ' in the UI but with 0 in the API.
           strFrom = "visControlPoint" _
               & CStr(intFromData - visControlPoint + 1)
       End If
       
       ' Get the To information.
       ' Use intToData to determine the type of shape to
       ' which the connector is connected.
       
       Set vsoShapeTo = vsoConnect.ToSheet
       
       strToType = whatkindofShape(vsoShapeTo, strSolution)
       intToId = vsoShapeTo.ID
       intToData = vsoConnect.ToPart
       
       'visConnectionPoint  = 100 + row index of connection point
       If (intToData < VisToParts.visConnectionPoint) Then
           
           ' in the v2007 sdk this is assigned to strFrom
           ' an error in the sdk
           strTo = vsoConnect.ToCell.Name
           
       Else
          ' Converts the VisToParts value to a string.
          ' Connection points are numbered starting with 1
          ' in the UI but with 0 in the API
         
          '
          ' check to see if the connection point has been named
          '
          Dim strCPName As String
          strCPName = ""
          'strCPName = parseConnectionPointName _
         '      (vsoConnect.ToCell.Name, intToData - visConnectionPoint + 1)
         
           If strCPName = "" Then
               strTo = "visConnectionPoint " _
                   & CStr(intToData - visConnectionPoint + 1)
           Else
               strTo = strCPName
           End If
                     
       End If
                       
       ' Display the information in the Debug window.
       If blnList = True Then
           Debug.Print strFrom & " of " _
               & "'" & vsoShapeFrom.Name & "'" _
               & " is connected to " & strTo & " of " _
               & "'" & vsoShapeTo.Name _
               & "'" & " with Text: " & "'" _
               & vsoConnect.FromCell.Shape.Text & "'"
       End If
       
       If blnType = True Then
           Debug.Print intFromId & " " _
               & strFromType & " " _
               & intToId & " " _
               & strToType
       End If
       
   Next vsoConnect

   Exit Sub
   
getTwoDandOneD_Err:

   Debug.Print "getTwoDandOneD " & Err.Number & " " & Err.Description
       
End Sub






For the stuff I do, I need to know the specific connection points that are being used, so I had to include some generic string functions. They're called in the above code so I've included them here.



a simple macro to call it

Code Select Expand



Private Sub pfd_connect()

   dim visPage as visio.page
   Set visPage = application.activepage
   
   getTwoDandOneD visPage, "pfd"


End Sub



[Aleem]

hi Al, thanks for the reply. Um... is this supposed to be in the visual basic editor in visio or excel? When I try looking for the macro to run it nothing shows up.

[aledlund]

it get's copied into the visio document, and called as a macro. The output is sent to the immediate window as text. It's not complete and only intended to show how the data might be gathered.
The v2007 sdk navigate example is 'tuned' to actually accomplish a two-step operation. The first part is to find the connections and the second part is to walk them and show a partial/single 'path'. There are some exposures in it, and a trivial coding error.
This example is part of something I have been playing with, and based on your question of how to examine a  pfd drawing, which I extended to look at the example you posted.
al

[Aleem]

oh, so i need to use it in visio 2007 and not 2003? and do i need to install the sdk?


oh i see, i changed the private to public in this code, brought up the immediate window, ran the macro and part of the output was:

BeginX of 'Major PipelineR' is connected to visConnectionPoint 10 of 'Column' with Text: '?'
EndX of 'Major PipelineR' is connected to visConnectionPoint 6 of 'Column.2' with Text: '?'
BeginX of 'Major PipelineR.7' is connected to visConnectionPoint 14 of 'Column' with Text: '?'
EndX of 'Major PipelineR.7' is connected to visConnectionPoint 3 of 'Column.2' with Text: '?'

[Aleem]

hey Al, I need a little more help. The output is too much for the original file, the PFD i'm working with has over 725 pipelines and 1500 instruments so some of the output is getting lost. Is there anyway to send the output to a text file or excel?

[aledlund]

I suspect that is a lot more code than Chris would like me to post here. We should probably take this off-line. I can be found over on yahoo with edlund60014.
al

[Aleem]

ok I will try to contact you on yahoo