VB6 to produce Excel Graphs

[steveh]

Morning all

We have a bespoke operating system written in (I believe) VB6 from which I have to produce KPI reports to Excel for some clients every month which is not a problem. I now have a client that wants me to visulise some of the data in PIE and Stacked Bar charts, our program guy says this can't be done automatically but then again he still thinks the world is flat

Does anybody know if this is possible in VB to tell Excel to do this based on the downloaded data.

[HansV]

Yes, that's possible. Excel can be controlled from other applications - it's called Automation. MSKB article How to automate Microsoft Excel from Visual Basic explains how to do this, with an elaborate code example.

[steveh]

Yes, that's possible. Excel can be controlled from other applications - it's called Automation. MSKB article How to automate Microsoft Excel from Visual Basic explains how to do this, with an elaborate code example.

Thanks Hans

I will not pretend for one minute that I understand it but it might give our inhouse guy a break

Ta

[rory]

You may also want to let your client know that pie charts are very rarely a good choice for data visualisation.

[agibsonsw]

You may also want to let your client know that pie charts are very rarely a good choice for data visualisation.

@rory I would welcome a brief explanation if you don't mind

I can appreciate that 3D or exploded pies can be very deceptive - deceitful even - but what might the issue be with a 2D pie?

Perhaps it's the combination of colours that might be used, or even the positioning of labels? I might also assume that people's perception of slices in the upper half of the pie is different to that in the lower half..

Andy.

[HansV]

You have to "read" a pie chart by comparing the angle / area of slices. Human beans aren't very good at that.
See:
Please don't use pie charts
Pie Charts Are Bad
Why Pie Charts Are Evil
and many, many others...

[agibsonsw]

Hello Hans.

From the first reference you supplied "pie charts are really only useful when a small number of categories of data are far, far greater than others" confirms my thinking for 2D pies.

I might read up on this subject later/ at some point . Top of my head, I would assume that simple columns (of equal width) are most useful for comparing different categories, and (possibly smoothed) lines are most useful for time series.

And 'Sparklines' are most useful to Microsoft's marketing division

[rory]

Sparklines do have their uses.

I don't think I've seen more than about 2 pie charts that wouldn't be better represented as a bar or column chart. 3D pies are worse, but then 3D charts in general are terrible for the same reason pies are. (I don't count XYZ charts here, which have their place.)

And of course, there are exceptions to every rule!

You may, time permitting, want to have a rummage through some of Stephen Few's stuff at perceptualedge or one of Tufte's books. Pretty good benchmarks.

[agibsonsw]

Thank you rory.

I was searching for a proper charting application a little while back. Free ones I discovered tended to be quite academic - lines and surfaces for mathematicians/students - or extremely expensive packages.

I intend to look into this subject in more detail at some point, but it's not something I need for my work currently.

Regards, Andy.