Using VBA create Org Chart in Visio using Excel Data

[NewUserMo]

Hello All,

I am trying to automate multi-page visio document. I am familiar with the Business Organization Chart Wizard, but it is very time consuming given I have 10 reporting line managers and my org chart is constantly changing.

Is it possible using VBA based on Excel data to create a Visi Oorg Chart and create a new page in the org chart when a new manager shows up. For example - with the data below I want two pages in Visio for Mike Huss and Allen Chu

Employee Report to Business Unit
Joe Smith Mike Huss Accounting
Angela Jo Mike Huss Accounting
Don Rodgers Allen Chu Operations
Don Roberts Allen Chu Operations

Would appreciate any help.

Thanks

 

[mintjulep]

It certainly should be possible.

Adding new information should be relatively straight forward.

Deleting existing information more difficult. You would probably want to record unique ID's for each box and connector in your spreadsheet as you create things, then you can flag them for deletion.

I have no clue what Visio's object model looks like, but as a guess:

People boxes have a unique identifier or index.

Connectors have a .Start and a .End property.

Probably there is something to control where a connector attaches to a box.

 

[NewUserMo]

Hi mintjulep - thanks for the feedback.

One more thing I forgot to mention in the post yesterday senior manager and then sub managers. Any help would be greatly appreciated.


Director Manager Employee Business Unit
John Adams Mike Huss Joe Smith Accounting
Mike Huss Angela Ho Accounting
Alex Hu Don Roberts Operations 
Alex Hu Michael Roberts Operations 
James Smith Luis Kline Karen Smith Trading
Sarah Apple Angel June Unix
Sarah Apple Kevin Run Unix

 

[NewUserMo]

I found this code but and it opens up visio not sure how to use it to incorporate my excel workbook. Any help would be greatly appreciated.

Sub Main()

'Read the command line
Dim strCommand As String
strCommand = Command$

'Check if the add-on exists
On Error GoTo OrgDoItErrHandler
Dim objVisio As Object
Dim objAddOn As Object

Set objVisio = CreateObject("Visio.Application")
Set objAddOn = objVisio.Addons.ItemU("OrgCWiz")

'Run the add-on (errors are handled by the add-on)
On Error Resume Next

'Break up the string
Dim strCommandPart As String
Dim strCommandLeft As String
strCommandLeft = strCommand
objAddOn.Run ("/S-INIT")
While (Len(strCommandLeft) > 0)
strCommandPart = Left(strCommandLeft, MAX_ARGSTRING_LENGTH)
strCommandLeft = Mid(strCommandLeft, Len(strCommandPart) + 1)
objAddOn.Run ("/S-ARGSTR " + strCommandPart)
Wend
objAddOn.Run ("/S-RUN " + strCommandLeft)

Exit Sub

OrgDoItErrHandler:

End Sub

 

[NewUserMo]

This had to come up before? Any help would be appreciated.

 

[dhookom]

Have you looked at how to create an org chart based on a data file?

Duane
Hook'D on Access
MS Access MVP

 

[mintjulep]

Visio seems to have a macro recorder.

I'd start by drawing a simple org chart while the recorder is recording.

That should get you sufficient insight into the object model to see what objects, properties and methods you need to work with.

 

[NewUserMo]

Hello,

Tried that and it did not record each step. I am just stuck...