EXCEL ORGANIZATION CHART ADD-IN

[babsannjoy]

I have installed the Organization Chart add-in into EXCEL 2010.
Using EXCEL 2010 VBA code I want to add an organization chart then add nodes. I also want to populate the nodes using text from an imported .CSV file located on another worksheet.
Here is what I have so far:

sub org_chart
ActiveSheet.OLEObjects.Add(ClassType:="OrgPlusWOPX.4", Link:=False, _
DisplayAsIcon:=False).Activate

Windows(1).WindowState = xlMaximized

Windows(1).Activate

'--trying to execute the F2 key via VBA - not working

Application.SendKeys ("f2")

end sub


The Organization chart add-in object window appears and when I manually press F2 twice (shortcut key) it adds a node to the org chart. I want to do this programmatically. Would like to use other shortcut keys to add different types of nodes and use the EDIT TEXT shortcut keys to add text to the boxes. Any suggestions ??? If the solution is to use a SMARTART organization chart object I am open to that solution as well.

 

[SkipVought]

Hi,

Have you checked VBA help?

You can also discover various objects, properties, variable values as you debug your code using, faq707-4594


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

have you tried your macro recorder?

Here's some very general code/help such as it is

Sub org_chart()
    Dim oChart As Object, oNode As Object, xl As Application
    
    Set xl = Application
    
    Set oChart = .OLEObjects.Add(ClassType:="OrgPlusWOPX.4", Link:=False, _
    DisplayAsIcon:=False)
    
    With oChart
    'name the object
        .Name = "Top Dog"
    'size & position the object
        .Top = 10
        .Left = 50
        .Width = 60
        .Height = 25
    'add a node -- this is just a guess at what METHOD you might find???
    'it may be of a totally different form???
    'there may be other arguments related to position???
        Set oNode = .Nodes.Add
        
        With oNode
        'do stuff here for the node like add text
            .Caption = xl.Index([YourReturnValueColumnRange], xl.Match([YourlookupValue], [YourLookupColumnRange], 0), 1)
        End With
        
    End With
    
    Set oNode = Nothing
    Set oChart = Nothing
    Set xl = Nothing
End Sub

Just made some educated guesses, based on how objects are often referenced and manipulated.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[babsannjoy]

Skip - I have done some research on the web alot of what I have found refers to EXCEL 2003 code which does not seem to work. I tried adding a watch but I can not seem to assign the chart to an object so that I can look at its properties. I have also tried recording a MACRO. All I get is the line of code adding the org chart to the spreadsheet. Any keys I hit (like f2 etc..)after to modify the chart do not appear in the MACRO. Any other ideas I can try?

Thanks for your reply
Babs

 

[SkipVought]

The cheat is an object in the sheet object...

With ActiveSheet.objects

End with

If you examine ActiveSheet.objects in watch you may discover something.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What am I thinking!!!

ActiveSheet.Shapes

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[babsannjoy]

Thanks so much Skip .. I will try all of your suggestions and let you know how I make out.

 

[SkipVought]

When you examine Shapes, it will indicate how many. You can drill down into each individual Item. In any particular Item object may be a Nodes object, and drilling down into the Nodes object you will find Items, which are individual Nodes.

Check out the ShapeNodes object in VBA help. There is an Insert method and other methods that can be used to manipulate a node. Also lists the properties of a Node.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[babsannjoy]

Skip - sorry it took so long to write back .. things got busy this week.
I decided to go with the SMARTART org chart object. Found the reference# by using the commented code below.
Here is what seems to work getting nodes added. Still have more to figure out...thanks for all your help for getting me over the ADDNODE hump !


Sub org_chart()

' needed below code needed initially to get the reference#
' find out the index/# of all the SMARTARTlayouts objects
' find the # of the organization chart by using the LOCALS window
'==========
'
' Dim WdSmartArtLayouts As SmartArtLayouts
' Set WdSmartArtLayouts = Application.SmartArtLayouts
' For i = 1 To WdSmartArtLayouts.Count
' Debug.Print i; WdSmartArtLayouts(i).Name
' Next i
'============

Dim oSAlayout As SmartArtLayout
Dim node As Object

'This works -- setting for org chart 96 is the reference# for the org chart hierarchy

Set oSAlayout = Application.SmartArtLayouts(96)

Set oshp = ActiveWorkbook.ActiveSheet.Shapes.AddSmartArt(oSAlayout)

With oshp
'name the object
.Name = "THE LIST"
'size & position the object
.Top = 10
.Left = 50
.Width = 600
.Height = 600
End With


'' delete previous shapes - may have to increase loop counter

For i = 1 To 5
oshp.SmartArt.AllNodes(1).Delete
Next

' adds 22 nodes

For i = 1 To 22
Set node = oshp.SmartArt.AllNodes.Add
If i = 1 Then
nodetext1 = Sheets("data").Range("A" & i)
nodetext2 = Sheets("data").Range("C" & i)
End If
' append text in 2 cells together and set text in node box

nodetext1 = Sheets("data").Range("B" & i)
nodetext2 = Sheets("data").Range("C" & i)
nodetext = nodetext1 & vbCrLf & nodetext2

node.TextFrame2.TextRange.Text = nodetext

' text in the node box set to black
node.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbBlack

'-- Left- TO DO --
' determine how to add nodes horizontally and vertically based on the values on Sheet("DATA")
' background of the box needs to be white or pale yellow not blue
' border around nodes needs to be black for now but should change based on the values/levels down from the root
' 1st level black
' 2nd level red
' 3rd level blue
' 4th level green
'----------
' -- below is not working --yet
node.TextFrame2.TextRange.linecolor.solidline = vbBlack

node.TextFrame2.TextRange.BackColor.RGB = vbWhite

Next i
End Sub

 

[SkipVought]

Sub org_chart()

' needed below code needed initially to get the reference#
' find out the index/# of all the SMARTARTlayouts objects
' find the # of the organization chart by using the LOCALS window
'==========
 '
' Dim WdSmartArtLayouts As SmartArtLayouts
' Set WdSmartArtLayouts = Application.SmartArtLayouts
' For i = 1 To WdSmartArtLayouts.Count
' Debug.Print i; WdSmartArtLayouts(i).Name
' Next i
'============

    Dim oSAlayout As SmartArtLayout
    Dim node As Object
    Dim oShp As Object, i As Integer, nodetext1 As String, nodetext2 As String, nodetext As String
    Dim r As Range
    
    'This works -- setting for org chart 96 is the reference# for the org chart hierarchy
    
     Set oSAlayout = Application.SmartArtLayouts(96)
    
    Set oShp = ActiveWorkbook.ActiveSheet.Shapes.AddSmartArt(oSAlayout)
    
    With oShp
        'name the object
        .Name = "THE LIST"
        'size & position the object
        .Top = 10
        .Left = 50
        .Width = 600
        .Height = 600
    End With
    
    
    '' delete previous shapes - may have to increase loop counter
    
'[b]I didn't have time to check this out, but the node count ought to be associated with this loop[/b]
     For i = 1 To oShp.SmartArt.Nodes.Count
        oShp.SmartArt.Nodes(i).Delete
    Next
    
'[b]today it might be 22, but isn't that base on the number of rows on your sheet"[/b]
    ' adds 22 nodes
    
    For Each r In Range(Cells(1, 1), Cells(1, 1).End(xlDown))
        i = i + 1
        
        Set node = oShp.SmartArt.AllNodes.Add
        If i = 1 Then
            nodetext1 = Sheets("data").Range("A" & i)
            nodetext2 = Sheets("data").Range("C" & i)
        End If
        ' append text in 2 cells together and set text in node box
        
 '[b]you never get the value in A1!!![/b]
        nodetext1 = Sheets("data").Range("B" & i)
        nodetext2 = Sheets("data").Range("C" & i)
        nodetext = nodetext1 & vbCrLf & nodetext2
        
        node.TextFrame2.TextRange.Text = nodetext
        
         ' text in the node box set to black
         node.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbBlack
        
        '-- Left- TO DO --
        ' determine how to add nodes horizontally and vertically based on the values on Sheet("DATA")
         ' background of the box needs to be white or pale yellow not blue
        ' border around nodes needs to be black for now but should change based on the values/levels down from the root
         ' 1st level black
        ' 2nd level red
        ' 3rd level blue
        ' 4th level green
        '----------
        
'[b] this may work for you [/b]
        node.Shapes.Line.ForeColor.RGB = vbBlack
        
        node.Shapes.Fill.BackColor.RGB = vbWhite
    
    Next i
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[babsannjoy]

Skip - saw your comments.. Thanks so much. I agree the max loop counts will change based on the rows on SHEET("data") just trying to get as simple program working first. I plan on using A1 cell value as the root of the org chart. I will try your suggestions on the fore and back colors. Have a great weekend.

 

[SkipVought]

If you want to drive this process from the data on your sheet, consider a table structure that includes the grouping for the org chart. It seem that you may be working toward a process that produces a number of charts.

So I would use a Structured Table with a new separate column for the data that you have in A1. So A1 would be repeated for each row in chart 1. Your process would create and position a new chart when it encounters a new value in that column.

Have I read your intent correctly?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[babsannjoy]

Thanks for the idea Skip. I haven't gotten the detailed specs so I am not sure whether it will be one org chart or several but I will do some research on table structures as well as how to add nodes horizontally and vertically. I will keep you updated. Thanks again for all you help on this .. Would not have been able to get this far with out your help.

 

[babsannjoy]

Skip-Is it best to add nodes from left to right per level (horizontally) or is it best to add nodes from top to bottom(vertically) then left to right. No matter which way I don't know how to retain the parent nodes so that nodes can be added below or adjacent to a child node.
The max vertical levels is 5 but the number of horizontal nodes will vary. I found out that I will be only creating one chart at a time on 1 sheet.
I looked at table structures I don't think thats the way to go. The source data is coming from an external source formatted as a .CSV file. I can format the .CSV file however I need it. I have new code to create the org chart. Below is code if the source data tells me to generate nodes for level 2. Let me know if you would like me to post the whole procedure and some simple sample data.
Still having problems getting those connecting lines set to black.

Any advice will be helpful.

crow - is set to the row count of the source data sheet
'second level of the org chart
If Sheets("data").Range("B" & crow) = 2 Then ' column B tells the org chart level
'
' first time thru add the 1st node below after the above node - then add assistant nodes after

If second_level_sw = True Then

Set nodes_lvl2 = rootnode.AddNode(msoSmartArtNodeBelow)

nodes_lvl2.Shapes.Line.ForeColor.RGB = vbBlack
nodes_lvl2.Shapes.Line.BackColor.RGB = vbBlack
nodes_lvl2.Shapes.Line.ForeColor.SchemeColor = vbBlack

nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
nodes_lvl2.TextFrame2.TextRange.Text = nodetext
second_level_sw = False
Else
Set nodes_lvl2 = rootnode.AddNode(msoSmartArtNodeTypeAssistant)
nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
nodes_lvl2.TextFrame2.TextRange.Text = nodetext

End If

nodes_lvl2.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbBlack
nodes_lvl2.Shapes.Line.ForeColor.RGB = vbBlack
nodes_lvl2.Shapes.Line.ForeColor.SchemeColor = vbBlack
nodes_lvl2.Shapes.ShapeStyle = msoShapeStylePreset2

 

[SkipVought]

can you record a macro setting the color?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[babsannjoy]

no.. recording a macro does not help. tried the below without success. Can only change manually. I think it has to do with Primary Theme Colors. I will do some more research tomorrow.

node.Shapes.ShapeStyle = msoLineStylePreset8

 

[babsannjoy]

Well Skip.. I have an update. I have the org chart logic working probably 95% done. I am generating the nodes from top to bottom then left to right. The logic is generating nodes down to the 4th level still have to test for the max level of five. But what is really frustrating is that I can not get those connector lines to change from the default color to black. These lines are automatically generated when I add a node. Darn things!!! I haven't quite given up yet.. will continue to research.
Have a nice weekend.

 

[SkipVought]

It would help if you posted your source data table and the current code that is working for you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

try setting up the DESIGN on the sheet, as Primary (Black) and then change the node line colors if you don't
want the nodes black. I can't seem to find a way to reference the connection lines in these charts either.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[babsannjoy]

Here is the latest code...

' global variable

Public nodetext As String

Sub org_chart()

Dim oSAlayout As SmartArtLayout

' nodes for each level of chart
Dim rootnode As SmartArtNode
Dim nodes_lvl2 As SmartArtNode
Dim nodes_lvl3 As SmartArtNode
Dim nodes_lvl4 As SmartArtNode
Dim nodes_lvl55 As SmartArtNode


Dim lastrow As Long ' last row in spreadsheet
Dim rowcount As Long ' count of rows in spreadsheet
Dim crow As Long ' current row

' logical switches for each chart level
Dim second_level_sw As Boolean
Dim third_level_sw As Boolean
Dim four_level_sw As Boolean
Dim five_level_sw As Boolean
Dim node_added_sw As Boolean

Dim oshp As Shape

'turn alerts off
Application.DisplayAlerts = False
Application.ScreenUpdating = False

' determine the last row in the sheet that contains data to be
' loaded into the chart

ActiveWorkbook.Sheets("data").Select
lastrow = ActiveSheet.UsedRange.Rows.Count

ActiveWorkbook.Sheets("sheet1").Select

' delete previous shapes - from previous execution

ActiveSheet.Delete

' add new sheet
ActiveWorkbook.Sheets.Add

' Setting for org chart 96 is the reference# for the org chart hierarchy

Set oSAlayout = Application.SmartArtLayouts(96)
Set oshp = ActiveWorkbook.ActiveSheet.Shapes.AddSmartArt(oSAlayout)

' shape formatting

With oshp
'name the object
.Name = "WBS LIST"
'size & position the object
.Top = 10
.Left = 50
.Width = 600
.Height = 600
End With

' delete default nodes -added automatically when the layout is added
For i = 1 To 5
oshp.SmartArt.AllNodes(1).Delete
Next i


' track what row in the spreadsheet is being read - rowcount
' track the chart row where the level info exists

rowcount = 1
crow = 1

' stop processing once all the rows in the spreadsheet have been read

Do Until rowcount >= lastrow

' set switches for determining that first node of the level has been added
' true assumes node on that level will be added
' when set to false node has been added

second_level_sw = True
third_level_sw = True
four_level_sw = True
five_level_sw = True

' set switch that determines whether a node has been added to the chart
' False assumes no node has been added to the chart

node_added_sw = False

' only added one node at a time depending of the value of the level in column B
' Column B in the data sheet contains the ORG CHART LEVEL the data pertains to.

Do Until node_added_sw = True


' add the root of the org chart (first level) and set the text

If Sheets("data").Range("B" & crow) = 1 Then
Set rootnode = oshp.SmartArt.AllNodes.Add

' append text in 2 cells together and set text in box
nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
rootnode.TextFrame2.TextRange.Text = nodetext
' format the node - maybe too much here
rootnode.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbBlack
rootnode.Shapes.Line.ForeColor.RGB = vbBlack
rootnode.Shapes.ShapeStyle = msoShapeStylePreset1
node_added_sw = True
End If

'second level of the org chart
If Sheets("data").Range("B" & crow) = 2 Then
'
' first time thru add the 1st node below after the above node - then add assistant nodes after

If second_level_sw = True Then

Set nodes_lvl2 = rootnode.AddNode(msoSmartArtNodeBelow)
nodes_lvl2.Shapes.Line.ForeColor.RGB = vbBlack
nodes_lvl2.Shapes.Line.BackColor.RGB = vbBlack
nodes_lvl2.Shapes.Line.ForeColor.SchemeColor = vbBlack

nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
nodes_lvl2.TextFrame2.TextRange.Text = nodetext
second_level_sw = False
node_added_sw = True
Else
Set nodes_lvl2 = rootnode.AddNode(msoSmartArtNodeTypeAssistant)
nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
nodes_lvl2.TextFrame2.TextRange.Text = nodetext
node_added_sw = True

End If

'format the node and connector line?
nodes_lvl2.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbBlack
nodes_lvl2.Shapes.Line.ForeColor.RGB = vbBlack
nodes_lvl2.Shapes.Line.ForeColor.SchemeColor = vbBlack
nodes_lvl2.Shapes.ShapeStyle = msoShapeStylePreset2


End If


'third level of the org chart children of the second level

If Sheets("data").Range("B" & crow) = 3 Then

' first time thru add the 1st node below after the above node - then add assistant nodes after

If third_level_sw = True Then
Set nodes_lvl3 = nodes_lvl2.AddNode(msoSmartArtNodeBelow)
nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
nodes_lvl3.TextFrame2.TextRange.Text = nodetext
third_level_sw = False
node_added_sw = True
Else
Set nodes_lvl3 = nodes_lvl2.AddNode(msoSmartArtNodeAfter, msOrgChartLayoutBothHanging)
nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
nodes_lvl3.TextFrame2.TextRange.Text = nodetext
node_added_sw = True

End If

nodes_lvl3.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbBlack
nodes_lvl3.Shapes.Line.ForeColor.RGB = vbBlack
nodes_lvl3.Shapes.Line.ForeColor.SchemeColor = vbBlack
nodes_lvl3.Shapes.ShapeStyle = msoShapeStylePreset3
End If


If Sheets("data").Range("B" & crow) = 4 Then
'
' first time thru add the 1st node below after the above node - then add assistant nodes after

If four_level_sw = True Then
Set nodes_lvl4 = nodes_lvl3.AddNode(msoSmartArtNodeBelow)
nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
nodes_lvl4.TextFrame2.TextRange.Text = nodetext
four_level_sw = False
node_added_sw = True
Else
'Set nodes_lvl4 = nodes_lvl3.AddNode(msoSmartArtNodeAfter, msOrgChartLayoutBothHanging)
Set nodes_lvl4 = nodes_lvl3.AddNode(msoSmartArtNodeBelow, msOrgChartLayoutBothHanging)
nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
nodes_lvl4.TextFrame2.TextRange.Text = nodetext
node_added_sw = True


End If
nodes_lvl4.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbBlack
nodes_lvl4.Shapes.Line.ForeColor.RGB = vbBlack
nodes_lvl4.Shapes.ShapeStyle = msoShapeStylePreset4

End If

If Sheets("data").Range("B" & crow) = 5 Then

' first time thru add the 1st node below after the above node - then add assistant nodes after

If five_level_sw = True Then
Set nodes_lvl5 = nodes_lvl4.AddNode(msoSmartArtNodeBelow)
nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
nodes_lvl5.TextFrame2.TextRange.Text = nodetext
five_level_sw = False
node_added_sw = True
Else
' Set nodes_lvl5 = nodes_lvl4.AddNode(msoSmartArtNodeAfter, msOrgChartLayoutBothHanging)
Set nodes_lvl5 = nodes_lvl4.AddNode(msoSmartArtNodeBelow, msOrgChartLayoutBothHanging)
nodetext = Sheets("data").Range("C" & crow) & vbCrLf & Sheets("data").Range("D" & crow)
nodes_lvl5.TextFrame2.TextRange.Text = nodetext
node_added_sw = True

End If
nodes_lvl5.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbBlack
nodes_lvl5.Shapes.Line.ForeColor.RGB = vbBlack
nodes_lvl5.Shapes.ShapeStyle = msoShapeStylePreset5

End If
crow = crow + 1
Loop
rowcount = rowcount + 1
Loop

End Sub