VB6 to produce Excel Graphs

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

VB6 to produce Excel Graphs

Post by 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 :grin:

Does anybody know if this is possible in VB to tell Excel to do this based on the downloaded data.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
HansV
Administrator
Posts: 79365
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VB6 to produce Excel Graphs

Post by 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.
Best wishes,
Hans

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: VB6 to produce Excel Graphs

Post by steveh »

HansV wrote: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
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
rory
5StarLounger
Posts: 822
Joined: 24 Jan 2010, 15:56

Re: VB6 to produce Excel Graphs

Post by rory »

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

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: VB6 to produce Excel Graphs

Post by agibsonsw »

rory wrote: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 :cheers:

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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 79365
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VB6 to produce Excel Graphs

Post by 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...
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: VB6 to produce Excel Graphs

Post by 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 :smile:. 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 :grin:
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
rory
5StarLounger
Posts: 822
Joined: 24 Jan 2010, 15:56

Re: VB6 to produce Excel Graphs

Post by 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.
Regards,
Rory

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: VB6 to produce Excel Graphs

Post by 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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.