Welcome to Telelogic Product Support
  Home Downloads Knowledgebase Case Tracking Licensing Help Telelogic Passport
Telelogic DOORS (steve huntington)
Decrease font size
Increase font size
Topic Title: hAdding charts to an Excel Workbook
Topic Summary: Managers just love pictures right?
Created On: 28-Sep-2007 10:53
Status: Post and Reply
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
Quick Reply Quick Reply
Subscribe to this topic Subscribe to this topic
E-mail this topic to someone. E-mail this topic
Bookmark this topic Bookmark this topic
View similar topics View similar topics
View topic in raw text format. Print this topic.
 28-Sep-2007 10:53
User is offline View Users Profile Print this message


Andrew Tagg

Posts: 151
Joined: 26-Oct-2004

Has anyone worked out a set of routines for adding charts to an excel workbook?

I already have a library for creating anew excel spreadsheet, adding new workbook, cells, text etc, will post that in my next post.

What I am struggling to do is translate the VBA for adding a chart to an existing workbook.

The VBA looks something like this:


Range("B14").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Graph Datasheet").Range("B14")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Graph Datasheet"


My overarching goal is to run an overnight job that logs into doors, scours the data and produces a daily report for the project progress, number of requirements, number of requirements not yet linked to a test, test results and so forth.

I don't know about you guys, but I can't get to the stage where manager types log into DOORS on a regular basis, but I want them to see the value of the data already in DOORS, so I need to present the data in a way that sits in their comfort zone.

I can already present a good text based spreadsheet, If I could have the graphs generated on the fly then it would add that certain polish to the exercise.

-------------------------
Andrew Tagg
Thales Air Systems, Melbourne
Australia.
andrew.tagg@thalesatm.com

Edited: 28-Sep-2007 at 11:09 by Andrew Tagg
Report this to a Moderator Report this to a Moderator
 28-Sep-2007 11:00
User is offline View Users Profile Print this message


Andrew Tagg

Posts: 151
Joined: 26-Oct-2004

OK, here is the library I use for generating my sreadsheets so far.

It is based largely on the work of Michael Sutherlands Advanced Excel Exporter, many many thanks for that Michael.

AllI have really done here is to grab Michaels code, then add some primitives to drive it, such as

Set text
Set cell bold
Set cell background colour

etc etc.

You will find my bits at the bottom, and all Michaels hard work at the top

Michael, let me know if you have any problem with all of this, and advice if I need to add your copyright or licence details to this.

Ta
Andrew.

-------------------------
Andrew Tagg
Thales Air Systems, Melbourne
Australia.
andrew.tagg@thalesatm.com
Report this to a Moderator Report this to a Moderator
 28-Sep-2007 11:01
User is offline View Users Profile Print this message


Andrew Tagg

Posts: 151
Joined: 26-Oct-2004

Oh yeah, this time I added the file :

-------------------------
Andrew Tagg
Thales Air Systems, Melbourne
Australia.
andrew.tagg@thalesatm.com
Report this to a Moderator Report this to a Moderator
 28-Sep-2007 13:48
User is offline View Users Profile Print this message


Andrew Tagg

Posts: 151
Joined: 26-Oct-2004

And here is a short program to illustrate driving excel with the above include file:

-------------------------
Andrew Tagg
Thales Air Systems, Melbourne
Australia.
andrew.tagg@thalesatm.com
Report this to a Moderator Report this to a Moderator
 28-Sep-2007 14:26
User is offline View Users Profile Print this message


Kevin Murphy

Posts: 206
Joined: 15-Jul-2005

Andrew,

You appear to have made excellent progress, but I can't help thinking that doing this in DXL is doing it the hard way.

Why not save a macro to your personal excel workbook that can create a chart on the fly based on the data in sheet 1? All you then have to have DXL do is open a workbook, populate it, and run the macro.

This can be done in DXL, but boy, is it a pain, especially maintenance.

Just my opinion....

-------------------------
Kevin Murphy
http://www.baselinesinc.com
The Requirements Management Experts
Report this to a Moderator Report this to a Moderator
 28-Sep-2007 14:47
User is offline View Users Profile Print this message


Andrew Tagg

Posts: 151
Joined: 26-Oct-2004

True enough, that was my next plan, I already do that with word files. My only problem with that was not knowing how to send though the bounds for the data grab if the size changes..

-------------------------
Andrew Tagg
Thales Air Systems, Melbourne
Australia.
andrew.tagg@thalesatm.com
Report this to a Moderator Report this to a Moderator
 28-Sep-2007 15:32
User is offline View Users Profile Print this message


Kevin Murphy

Posts: 206
Joined: 15-Jul-2005

Andrew,

Assuming you need to know what the last cell is (I believe your first cell will probably always be A1 or A2)....

You do it like this:

-------------------------
Kevin Murphy
http://www.baselinesinc.com
The Requirements Management Experts
Report this to a Moderator Report this to a Moderator
 28-Sep-2007 15:41
User is offline View Users Profile Print this message


Andrew Tagg

Posts: 151
Joined: 26-Oct-2004

Ahhh, great, Thanks Kevin. Will give that a go on Monday. Have a good weekend.

-------------------------
Andrew Tagg
Thales Air Systems, Melbourne
Australia.
andrew.tagg@thalesatm.com
Report this to a Moderator Report this to a Moderator
 28-Sep-2007 19:34
User is offline View Users Profile Print this message


David Pechacek

Posts: 674
Joined: 5-Dec-2006

Here are the Excel Color Values for the standard drop down for selecting text color and background color.
Also almost every different cell number format.

And for creating charts I also just create a macro in a workbook and use that to create charts. Far easier than programming one in DXL. Just put the data where its supposed to go and call the macro to create the chart.

-------------------------
David Pechacek
AAI Services Textron
dpechacek@sc-aaicorp.com
David.Pechacek@gmail.com

Edited: 28-Sep-2007 at 19:35 by David Pechacek
Report this to a Moderator Report this to a Moderator
 30-Sep-2007 10:55
User is offline View Users Profile Print this message


Andrew Tagg

Posts: 151
Joined: 26-Oct-2004

Thanks David,will add those into the mix.

-------------------------
Andrew Tagg
Thales Air Systems, Melbourne
Australia.
andrew.tagg@thalesatm.com
Report this to a Moderator Report this to a Moderator
Statistics
20925 users are registered to the Telelogic DOORS forum.
There are currently 2 users logged in.
The most users ever online was 15 on 15-Jan-2009 at 16:36.
There are currently 0 guests browsing this forum, which makes a total of 2 users using this forum.
You have posted 0 messages to this forum. 0 overall.

FuseTalk Standard Edition v3.2 - © 1999-2009 FuseTalk Inc. All rights reserved.