How to draw a chart in Excel using VBScript?


How to draw a chart in Excel using VBScript?
A picture is worth a thousand words–Napoleon Bonaparte.

Yeah! It sounds great; but it needs an effort to display thousand words into a single image,well, i am talking about drawing chart in excel.
Sometimes, i wonder why not QTP provide all this features;How nice it would be; entering range of values in the data table and just on a single click desired type of graph would be generated.
It’s often said that “Hope makes life more interesting to live” :)

It would be easier to display our automation test coverage in a graphical format to our stakeholders.we can achieve it as follows;

Steps to be performed:

Create an Instance of Excel–>Add a workbook–>Add a sheet–>Add range of datas–>Select range of datas–>Add a chart–>Populate data–>format a chart–>Display a graph.

Code:

'**********************************************
'Function: CreateChart()
'Description:Creates a graph in an excel sheet.
'Author:QTP Lab:--A touch of madness
'Website:http://automationlab09.wordpress.com
'Last modified:29/04/2009

'****************************************************
Function CreateChart()

On Error Resume next
Dim oExl,oWrkbk,oWrkst,oMychart

Set oExl=CreateObject("Excel.Application")

With oExl

        .Visible=True

End With

Set oWrkbk=oExl.Workbooks.Add()
Set oWrkst=oWrkbk.Worksheets(1)

With oWrkst
.Cells(1,1)="Critical"
.Cells(2,1)="Very Serious"
.Cells(3,1)="Serious"
.Cells(4,1)="Moderate"
.Cells(5,1)="Mild"

.Cells(1,2)="Bugs Severity"

For i=2 to 5

.Cells(i,2)=i+21

 If i>4 Then
 .Cells(5,2)=9
 End If

Next

End With
Set oRange=oWrkst.UsedRange
oRange.Select
Set oChart=oExl.charts
oChart.Add()
Set oMychart=oChart(1)
oMychart.Activate
oMychart.ChartType=5
oMychart.ApplyDataLabels 5

oMychart.PlotArea.Fill.Visible=False
oMychart.PlotArea.Border.LineStyle=-4142
oMychart.SeriesCollection(1).DataLabels.Font.Size=15
oMychart.SeriesCollection(1).DataLabels.Font.ColorIndex=2

oMychart.ChartArea.Fill.Forecolor.SchemeColor=49
oMychart.ChartArea.Fill.Backcolor.SchemeColor=14
oMychart.ChartArea.Fill.TwoColorGradient 1,1

oMychart.ChartTitle.Font.Size=20
oMychart.ChartTitle.Font.ColorIndex=4
'oWrkbk.Close
Set oExl=Nothing

If err.number<>0 then

 Msgbox "error occurred while drawing..."
 Msgbox err.Description

Else

 Msgbox  "Successfully drawn"

End If

End Function

CreateChart()

Thank You :)

Output:

Chart-in-excel-with-VBscript

Chart-in-excel-with-VBscript

About these ads

11 responses to this post.

  1. Posted by Priya on May 8, 2010 at 10:28 am

    Qtp tester,

    I am really impressed by this blog and your outlook toward the issues..

    Thank you

    Reply

  2. Posted by Rajan on August 20, 2010 at 3:26 pm

    great solution for me.

    Reply

  3. Posted by Rajan on August 20, 2010 at 3:31 pm

    can I get some more properties and its info to edit and modify chart using vbs….

    Reply

  4. Posted by phucdull on September 23, 2010 at 8:47 am

    It’s amazing, thanks you

    Reply

  5. Thanks. I was using Javascript but had a major headache trying to pass the array to it from vbscript :)

    Reply

  6. Posted by derik on February 15, 2011 at 2:49 pm

    Good solution! Got question. How can we format datalables posions to insidebase?

    Reply

  7. Posted by Mouse on May 31, 2011 at 8:48 pm

    This is great. How would you resize the chart using VBS?

    Reply

  8. Posted by kiran on December 13, 2011 at 5:31 am

    How to get a value from a chart in flex application in QTP

    Reply

  9. I do accept as true with all the ideas you have introduced in your post. They’re really convincing and can definitely work. Nonetheless, the posts are too brief for newbies. May you please lengthen them a bit from next time? Thank you for the post.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: