Solved

Outputing Org Chart Relationship Data to Excel

Posted on 2010-11-11
7
604 Views
Last Modified: 2012-05-10
I've got a series of Org Charts for our company.  These Org Charts define Executive/Managers and reports to structures.  I'm trying to create a VBA macro that will output the name of the employee, and the reports to executive to an excel spreadsheet.  Since the org charts are seperate visio files, I'm going to use FSO to cycle through and open each file, and I know the excel object well enough to create the excel output.  My weak point is not knowing the visio object well enough to get the relationship data.  I've discovered how to cycle through each shape in a page, but I can't figure out how to determine what Master shape that the current shape is linked to?  If I can ID the master/linked shape, I should be able to extrat the custom text that I need from the shape.  Thanks in advance.
0
Comment
Question by:Calvin Brine
  • 4
  • 3
7 Comments
 
LVL 30

Accepted Solution

by:
Scott Helmers earned 500 total points
Comment Utility
You didn't specify your version of Visio. If you're using Visio 2010, let me know and ignore what's below because the work will be different and easier.

I think the code I provided for this question should get you started. As I commented in my response to that question, it is odd that Visio hasn't had a built-in notion of connectedness...

The code uses the Visio Connects collection to parse out what is at each end of every connector on the page. It then stores the text of the shapes at the ends of each connector in the connector itself (using shape data fields called "From" and "To").

In one sense this code is the reverse of what you want -- it examines every connector to determine what is at each end. You want to examine each endpoint shape and determine what's connected to it. Also, this code stores connectivity data in the connectors but you want to create a shape data field in each org chart shape called "ReportsTo" and store data there.

One more thing to know that will be useful: all org chart shapes contain a USER field called ShapeType; the shapes you care about have a value in that field that is <=6. The code I supplied for this question uses that idea to select shapes for processing. Come to think of it, you'll want to use this outer loop as your primary loop and then incorporate appropriate elements from the first code sample to determine what's at the end of the connectors.

Hope this helps... please feel free to come back with additional questions, especially if you get stuck in the Connects collection.

Regards,
Scott
 

0
 
LVL 16

Author Comment

by:Calvin Brine
Comment Utility
Scott,
 The code was what  I was looking for.  It allows me to define the from object and the to object.  The only problem I'm running into now, is extracting the DAMN CUSTOM Proprety values from the shape objects. I'm using
MsgBox shpFrom.CellsSRC(visSectionProp, 2, 0)
which works fine when the custom properties are values, but returns 0 when they hold text?  I've checked the options for the third parameter and I don't see any other possible constant that might hold text instead of values?  Any ideas?  I know I viewing the correct propterty because the .formula returns the label.
 
Thanks
Calvin
0
 
LVL 16

Author Comment

by:Calvin Brine
Comment Utility
OK, I did a little digging and figured out how to extract the string data on my own.  So, I now have a set of code that will extract the From->To reporting relationships for all visio Org files within a specfic folder, and dump this data out to an excel workbook.  It was an interesting exercise, and I'm posting the code here in case anyone else is interested in the future.

Cal
Sub GetFlowchartConnections()

' Gets text from shapes connected at each end of every line on page

' Stores text in two shape data fields on each line



    Dim pg As Visio.Page

    Dim shp As Visio.shape

    Dim cnxEndPoints As Visio.Connects

    Dim EP As Visio.Connect

    Dim shpFrom As Visio.shape

    Dim shpTo As Visio.shape

    Dim FSO As Object

    Dim f As Object

    Dim xlApp As Object

    Dim wb As Object

    Dim ws As Object

    Dim FilePath As String

    Dim vC As Visio.Cell

    

    On Error GoTo ErrHandler

    

    Set xlApp = CreateObject("Excel.application")

    xlApp.Visible = True

    

    Set wb = xlApp.workbooks.Add

    Set ws = xlApp.activesheet



    Set FSO = CreateObject("scripting.filesystemobject")



    'Get returned data folder.

    With xlApp.FileDialog(4)

        .AllowMultiSelect = False

        .ButtonName = "Select"

        .Title = "Select Processing Folder"

        .Show

        If .SelectedItems.Count = 0 Then

            MsgBox "Process Cancelled"

            End

        End If

        FilePath = .SelectedItems(1)

    End With





    For Each f In FSO.GetFolder(FilePath).Files

        If f.Type Like "*Visio*" Then

            Application.Documents.Open f.Path

    

            For Each pg In ActiveDocument.Pages

                For Each shp In pg.Shapes

                    'Call InitFromTo(shp)

                    

                    ' BeginX only exists if shape is a line

                    If shp.CellExists("BeginX", False) Then

                        'Get connects collection for current shape

                        Set cnxEndPoints = shp.Connects

                        

                        If cnxEndPoints.Count > 0 Then

                            For i = 1 To cnxEndPoints.Count

                                Set EP = cnxEndPoints(i)

                                If EP.FromPart = visBegin Then

                                    ' Get shape this end is attached to

                                    Set shpFrom = EP.ToSheet

                                    ' Store attached shape's text

                                    ws.Range("A65535").End(-4162).Offset(1, 0) = shpFrom.CellsU("Prop.Name").ResultStr(visNone)

    

                                Else

                                    ' Get shape this end is attached to

                                    Set shpTo = EP.ToSheet

                                    ' Store attached shape's text

                                    ws.Range("A655535").End(-4162).Offset(0, 1) = shpTo.CellsU("Prop.Name").ResultStr(visNone)

                                End If

                            Next i

                        End If

                    End If

                Next shp

            Next pg

            ActiveDocument.Close

        End If

    Next f

    Exit Sub

ErrHandler:

    wb.sheets.Add ws

    xlApp.activesheet.Range("A1") = Err.Number

    xlApp.activesheet.Range("A2") = Err.Description

    xlApp.activesheet.Range("A3") = EP.Name

    xlApp.activesheet.Range("A4") = EP.Name

    xlApp.activesheet.Range("A5") = f.Path

    

End Sub

Open in new window

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 16

Author Closing Comment

by:Calvin Brine
Comment Utility
Thanks Scott, couldn't have done it without your code sample.
0
 
LVL 30

Expert Comment

by:Scott Helmers
Comment Utility
I'm really glad you found the .ResultStr property... especially because I typed a reply to your question about extracting the text from the field... but looking above it's clear that I never clicked Submit!! I have no idea what happened as I would have been happy to save you the work of ferreting out that property.

BTW, in the post I never posted, I also suggested that you download the Visio 2007 SDK if you haven't already. It's invaluable: decent documentation and very helpful code samples.

Thanks for posting the code you created that worked. I appreciate it when people do that as it's very helpful for others.

Scott
0
 
LVL 30

Expert Comment

by:Scott Helmers
Comment Utility
I realize you now have the code to solve your problem but something just occurred to me -- Visio will create an Excel workbook that includes "reports to" info without you needing to do anything. Just select Export from the Org Chart menu...

This approach doesn't accomplish cycling through a folder full of org charts but it's simple and fast. And on the flip side, when you run the Import wizard, you can tell the wizard to store any data in the spreadsheet in the org charts shapes. Consequently, your rebuilt org chart shapes can have "reports to" as shape data in every shape.

Just a thought after the fact...

0
 
LVL 16

Author Comment

by:Calvin Brine
Comment Utility
Ah...I checked the reports menu and couldn't find anything to output the actual reports to relationships.  Ah well, I learned a few things about visio, and I had about 60 different documents to do manually anyway.(Don't ask me why they are not all stored in seperate pages of the same document, I didn't design them :-) )

I will check out this option just to see what it gives me for the future.

Thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

The Question Several times in recent years, someone has posted a question at EE asking whether they could display the Windows directory structure – folders and subfolders – using Visio. When one person specifically asked about displaying directory …
Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now