Excel report of processes and connections on visio diagram

Hi Experts,

Is there a way I can export a report from MS Visio which includes the contents of a visio diagram, including which processes are connected and what the description on the connectors are?

Thanks.
rav_ravAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
The answer is....yes... but not without some work.

The good news is that you can run VIsio reports and send the output to Excel. And the Visio flowchart template includes a pre-defined report. From the Visio main menu:
  • select Data/Reports...
  • select the Flowchart report
  • click Run
  • select Excel as the output format
  • click OK
That's the good news. The bad news is that the Visio report wizard only knows about shapes and the data they contain -- it can't report on connectivity. To do that requires writing macro code. If you'd like to give this a try, I can post something that will get you started -- let me know if that will be helpful.

Scott
0
rav_ravAuthor Commented:
Hey Scott,

Sorry for the delayed response -- was away from work.

I know about the reporting functionality with Visio and I faced the same problem as you mentioned above.

Anything you can post to get me started would be much appreciated.  Thanks.
0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
A couple of questions:

Did you use the standard Visio flowchart shapes to create your drawing? Or are they custom shapes?

Are the connector lines directional? If so, how do I identify "from" and "to"? Or is it sufficient to list what is at each end without any sense of direction?

What uniquely identifies each shape at the end of a line? Is there an ID or shape number? Or is there text on the shape? (I need to know how to report what's at the end of a line.)

Can you post a sample drawing? Or, if you prefer not make it public by posting it here, can you email one to me at the address in my profile? That will help me know what I'm dealing with as I put together a macro.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

rav_ravAuthor Commented:
Some answers...

1. Used standard Visio flowchart shapes

2. The connectors are directional.  There are arrows on the connector to indicate the "From" and "To".

3.  There is text in the shape to identify it uniquely.

I will email you a sample as I can't seem to attach a visio file.

Thanks for your help.
0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
I just discovered something that may provide you with useful code to do what you want. The Visio Guy web site (www.visguy.com) contains a wealth of cool Visio stuff, including this article on shape connectivity that is directly relevant to your question:
   http://www.visguy.com/2009/04/22/analyze-connectivity-between-process-flows/

Why don't you download and experiment with the diagram provided above to see whether it provides you with the type of information you want. Let me know if you have any questions about it.

If it is heading in the right direction, I will provide code that does something similar for your drawing but ultimately produces an Excel report for all connectors instead of a dialog box for one connector.

0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
For various reasons, I went ahead with creating a bit of code for you. It stores the text of the attached shapes in two shape data fields on each line. (The fields are not too subtly named "From" and "To"). From here you should be able to create a Visio report that will write the data to Excel.

Let me know if you need anything else.

Scott

NOTE: For historical reasons, Experts Exchange doesn't allow upload of Visio files (*.vsd), consequently, I changed the file extension of the attachment below to .txt. After you download the file, change the file extension back to .vsd and you'll be able to open the drawing with Visio.

Determine-flowchart-paths.txt
0
rav_ravAuthor Commented:
Hey Scott,

I was not able o open the file you attached.  I tried to change extension from TXT to VSD but it didn't work.

In any case, I played around with the link you provided on Visio Guy and was able to get a description of the connectors I need.  However, I was outputting the string descriptions of the connector to the Immediate Window (via Debug.Print).

I posted a separate question around on how to Export to Excel.  Once I got that answer, I thought I would post on this thread the solution I mishmashed together for your input, as you suggested a few days back.

I will try again to open your file tomorrow.  I've listed below the code I was playing around with.
--------------------------------------------------------------------

Public Sub tstConnector()
 
    Dim shpConnector As Visio.Shape
    Dim colConnectsCollection As Visio.Connects
    Dim intConnects As Integer
    Dim szPgName As String
    Dim szShpName As String
    Dim szConnectorName As String, szConnectorDesc As String
    Dim szPreShpDesc As String, szSucShpDesc As String
    Dim szShpComment As String
    Dim intShpCounter As Integer
    Dim pg As Page
    
    intConnects = 0
    intShpCounter = 0
      
    For Each pg In ActiveDocument.Pages
        For Each shpConnector In pg.Shapes
            
            szPgName = ""
            szShpName = ""
            szConnectorName = ""
            szConnectorDesc = ""
            szPreShpDesc = ""
            szSucShpDesc = ""
            szShpComment = ""
            
            szPgName = pg.Name
            szShpName = shpConnector.Name
                                   
            If shpConnector.OneD Then
                Set colConnectsCollection = shpConnector.Connects
                intConnects = colConnectsCollection.Count
                Select Case intConnects
 
                    Case 0
                        szShpComment = "This Connector is not Glued to anything at all."
 
                    Case 1
                        szConnectorName = shpConnector.Name
                        szConnectorDesc = shpConnector.Text
                        If colConnectsCollection.Item(1).ToSheet <> Nill Then
                            If colConnectsCollection.Item(1).FromPart = visBegin Then
                                szPreShpDesc = colConnectsCollection.Item(1).ToSheet.Text
                            ElseIf colConnectsCollection.Item(1).FromPart = visEnd Then
                                szSucShpDesc = colConnectsCollection.Item(1).ToSheet.Text
                            End If
                        End If
 
                    Case 2
                        szConnectorName = shpConnector.Name
                        szConnectorDesc = shpConnector.Text
                        If colConnectsCollection.Item(1).ToSheet <> Nill Then
                            If colConnectsCollection.Item(1).FromPart = visBegin Then
                                szPreShpDesc = colConnectsCollection.Item(1).ToSheet.Text
                            ElseIf colConnectsCollection.Item(1).FromPart = visEnd Then
                                szSucShpDesc = colConnectsCollection.Item(1).ToSheet.Text
                            End If
                        End If
 
                        If colConnectsCollection.Item(2).ToSheet <> Nill Then
                            If colConnectsCollection.Item(2).FromPart = visBegin Then
                                szPreShpDesc = colConnectsCollection.Item(2).ToSheet.Text
                            ElseIf colConnectsCollection.Item(2).FromPart = visEnd Then
                                szSucShpDesc = colConnectsCollection.Item(2).ToSheet.Text
                            End If
                        End If
                End Select
                                
                Debug.Print "--------------------"
                Debug.Print "Page Name: " & pg.Name
                Debug.Print "Shape Iteration #: " & intShpCounter
                Debug.Print "Shape Name: " & szShpName
                Debug.Print "Connector Name: " & szConnectorName
                Debug.Print "Connector Desc.: " & szConnectorDesc
                Debug.Print "Predecessor Shape Desc.: " & szPreShpDesc
                Debug.Print "Successor Shape Desc.: " & szSucShpDesc
                Debug.Print "Comment: " & szShpComment & vbCrLf & vbCrLf
 
            End If
            
            intShpCounter = intShpCounter + 1
            
        Next
    Next
  
End Sub

Open in new window

0
rav_ravAuthor Commented:
Hey Scott,

After a few tries, I was able to open your file and look at the code.  I also saw how the ShapeSheet properties have changed to include the From and To for the connectors.

How do I export these fields though?  I tried seeing if it was possible through Data --> Reports, but I couldn't find a place where I could select the new fields to be included in the report.

Any suggestions?

Thanks
0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
I had assumed that the fields on the connectors -- like any shape data fields -- would be available via the reports. But you're correct that for some reason the From and To fields don't appear in the fields list when you're defining a report.

I don't know why this is true but will investigate and let you know.
0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
I still haven't figured out why the shape data on the lines doesn't appear in the report wizard, but I changed the macro to create User fields instead of shape data fields and the report works correctly. See v2 of the macro in the Visio drawing below and run the report. It's not fancy but it's a step in the right direction.

As before, change the file extension back to VSD.

Determine-flowchart-paths-v2.txt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Forgot to mention the name of the report I created is "Flowchart Connectivity".
0
rav_ravAuthor Commented:
Hey Scott,

This is great.  Thank you.
0
rav_ravAuthor Commented:
Thanks for your help Scott.  
0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
You're welcome -- I'm glad this helped. Now all I need to do is figure out why the shape data fields on the connectors don't show up in the reports!
0
rav_ravAuthor Commented:
Hey Scott,

Please let me know if you find out the answer.  Thanks.
0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
The answer is user error -- my error. When my code created the shape data fields, I assigned values to them -- the connected shape text -- but didn't assign a name. More specifically, I did give names to the two shape data rows ("Prop.From" and "Prop.To") but I didn't put a name in the "Label" field. Adding a name to the "Label" field causes them to appear just fine in the report wizard.

 
0
rav_ravAuthor Commented:
Hey Scott,

What's the line of code I would need to add to specify a name in the "Label" field?

Thanks.
0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
You need to add one line of code for From and one for To. In the somewhat cryptic way that you set text values in a shapesheet cell from VBA code, you need these lines as shown in the modified sub below:
   shape.CellsU("Prop.From.Label").FormulaU = Chr(34) & "From" & Chr(34)
   shape.CellsU("Prop.To.Label").FormulaU = Chr(34) & "To" & Chr(34)

Note: be sure to use this code with the first sample I uploaded and not with the second one that uses User cells instead of Prop cells.

   

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

Open in new window

0
rav_ravAuthor Commented:
Thanks Scott. Works like a charm.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Visio

From novice to tech pro — start learning today.