Generate a chart in excel

Share this post
FaceBook  Twitter  Mixx.mn     

To Generate a chart in excel , use following code:
Steps:
1) create an excel application object
2) Set visible property as True
3) Add a workbook and worksheet
4) Add chart data headers
5) add chart data
6) Generate a chart
7) Set all attributes for chart i.e Charttype, height, width, font, color etc.


Code:-

'**********************************************
'Function: GenerateChart()
'Description:Creates a graph in an excel sheet.
'****************************************************
Function GenerateChart()

On Error Resume next
Dim ObjExcel,ObjWorkbook,ObjWorksheet,ObjChart
'1) create an excel application object'
Set ObjExcel=CreateObject("Excel.Application")

'2) Set visible property as True'
With ObjExcel
        .Visible=True
End With

'3) Add a workbook and worksheet'
Set ObjWorkbook=ObjExcel.Workbooks.Add()
Set ObjWorksheet=ObjWorkbook.Worksheets(1)

'4) Add chart data headers'
With ObjWorksheet
.Cells(2,1)="1-Critical"
.Cells(3,1)="2-Very Serious"
.Cells(4,1)="3-Serious"
.Cells(5,1)="4-Moderate"
.Cells(6,1)="5-Mild"

.Cells(1,1)="Bugs Severity"
.Cells(1,2)="Yr2009"
.Cells(1,3)="Yr2010"
.Cells(1,4)="Yr2011"
.Cells(1,5)="Yr2012"

'5) add chart data'
For i=2 to 6
  For k=0 to 3
   .Cells(i,2+k)=i+21+ (k+5)
 Next
Next

End With
Set oRange=ObjWorksheet.UsedRange
oRange.Select
'6) Generate a chart'
Set oChart=ObjExcel.charts
oChart.Add()
Set ObjChart=oChart(1)
ObjChart.Activate

'7) Set all attributes for chart i.e Charttype, height, width, font, color etc.'
ObjChart.ChartType=51' 5 xlXYScatterLines
ObjChart.ApplyDataLabels 5
   
ObjChart.PlotArea.Fill.Visible=False
ObjChart.PlotArea.Border.LineStyle=-4142
ObjChart.SeriesCollection(1).DataLabels.Font.Size=15
ObjChart.SeriesCollection(1).DataLabels.Font.ColorIndex=2

ObjChart.ChartArea.Height= 250
ObjChart.ChartArea.Width= 300
ObjChart.ChartArea.Fill.Forecolor.SchemeColor=49
ObjChart.ChartArea.Fill.Backcolor.SchemeColor=14
ObjChart.ChartArea.Fill.TwoColorGradient 1,1

ObjChart.ChartTitle.Font.Size=20
ObjChart.ChartTitle.Font.ColorIndex=4
Set ObjExcel=Nothing


End Function