Excel 2010 VBA is there a way to make an Org Chart from cells with personnel?

[DougP]

We have an Excel sheet with all employees which has their "reports to" (manager).
Is it possible to create an Org Chart using that data? using VBA? I have extensive VBA knowledge but none using charts.
There are thousands of employees and scads of depts and reports to...so typing and fiddling is not an option. It needs to be automated. can VBA manipulate the chart?

TIA

DougP

 

[SkipVought]

hi,

Given a [report to] value, you can write a recursive routine that takes that value and feeds it into an ADO query like...

sSQL = "Select [employee] " & _
"From [Excel sheet with all employees$] " & _
"where [report to] = '" & ReportToValue & "'"

This returns all employees reporting to the value suppled. Then each of the returned employees is fed into the procedure until no employees are returned.

Also keep track of the LEVEL of the drill-down, as this level indicates the relative level of the org chart.

I would write the results into a new sheet and then in a different process, create & position the shapes on the report sheet.

Skip,

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

 

[DougP]

Ok I have never used the SQL statement to refer to cells in Excel.
what do I pass it to? ADO recordset?
so lests say I have a User form with a combo box on it and I want to load it with the unigue managers names.
I have column headings in row one
I have "Name" for employee and "Manager" for Reports to. I know, but this is the data the way they gave it to me.

How do it pass a SQL string to load the combo box?

Then using your sSQL what do I do with it?


DougP

 

[SkipVought]

faq68-5829

No VBA required for plain vanilla. Some may be needed under certain circumstances.

Do your QueryTable on a sheet and reference the return data range as your listfillrange.

Skip,

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

 

[DougP]

Ok the query wizard wants a table, how do I refer to a TAB in Excel? I have the .xlsx file but there is no data.
them if I close it a re-open teh sheet the data source it says "This data source has no visible tables".
I did not see where to add a table from a worhseet TAB.

I just need to know how to add people to this Org Chart see attachment.
How do I use VBA to make or edit the chart?
Or how do I draw a square?
I am using Excel 2010.

DougP

 

[SkipVought]

If you have a sheet that contains one table, with one row of headings in ROW 1, then you have a table. In the MS Query Add Tables Window, hit the OPRIONS button and make sure that ALL boxes are checked.

Skip,

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

 

[DougP]

Alrighty then, I have a query which is looking at the same sheet I already have?
How do I fill in the Org chart with that?


DougP