Calvin Brine
asked on
Outputing Org Chart Relationship Data to Excel
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Thanks Scott, couldn't have done it without your code sample.
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
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
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...
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...
ASKER
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
I will check out this option just to see what it gives me for the future.
Thanks
ASKER
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(visSectio
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