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.


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

Function CreateChart()

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

Set oExl=CreateObject("Excel.Application")

With oExl


End With

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

With oWrkst
.Cells(2,1)="Very Serious"

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

For i=2 to 5


 If i>4 Then
 End If


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


oMychart.ChartArea.Fill.TwoColorGradient 1,1

Set oExl=Nothing

If err.number<>0 then

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


 Msgbox  "Successfully drawn"

End If

End Function


Thank You šŸ™‚





16 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


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

    great solution for me.


  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….


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

    It’s amazing, thanks you


  5. Thanks. I was using Javascript but had a major headache trying to pass the array to it from vbscript šŸ™‚


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

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


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

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


  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


  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.


  10. […] for the script I started with the example documented onĀ  and modified it to fit the process. All the credit goes to the original […]


  11. Great work, thank you. I extended your example to export just the image as PNG and receive all the labels&data as arguments. I have documented this on


  12. […] the VB Script is taken fromĀ  and modified it to fit the process. I do not own the copyright and all the credit goes to the […]


  13. Posted by Mayur on January 15, 2016 at 8:50 am

    Really this is a great post, however I need some more additional information that how we can get pie chart at same sheet where data is available, in above code it draws a pie chart in separate sheet


  14. I do believe all of the ideas you’ve presented on your post.
    They’re really convincing and will certainly work. Nonetheless, the posts are very quick for starters.
    Could you please lengthen them a bit from subsequent time?
    Thanks for the post.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: