How to create chart in Excel without using ChartWizard Object

I try to create chart in Excel from Navision without using ChartWizard object because I want to control chart object in Excel from Navision (Size of chart, series collection,etc). Here the sample codes :


//Excel Objet
//Walkthrough

Excel : 'Microsoft Excel 12.0 Object Library'.Application
Book : 'Microsoft Excel 12.0 Object Library'.Workbook
Range: 'Microsoft Excel 12.0 Object Library'.Range
Sheet : 'Microsoft Excel 12.0 Object Library'.Worksheet
Chart : 'Microsoft Excel 12.0 Object Library'.Chart
ChartObject : 'Microsoft Excel 12.0 Object Library'.ChartObject
ChartObjects : 'Microsoft Excel 12.0 Object Library'.ChartObjects
Series : 'Microsoft Excel 12.0 Object Library'.Series
SeriesCollection : 'Microsoft Excel 12.0 Object Library'.SeriesCollection
Axis : 'Microsoft Excel 12.0 Object Library'.Axis

IF NOT CREATE(Excel) THEN
CREATE(Excel);
Excel.Visible(FALSE);

Book := Excel.Workbooks.Add(-4167);
Sheet := Excel.ActiveSheet;
Sheet.Name := 'SHEET NAME';

//Sample Data
Sheet.Range('A2').Value := 'FY2008';
Sheet.Range('A3').Value := 'FY2009';
Sheet.Range('B1').Value := 'Sales Sept';
Sheet.Range('C1').Value := 'YTD Sept';
Sheet.Range('D1').Value := 'YTD Budget';
Sheet.Range('E1').Value := 'Collection';
Sheet.Range('F1').Value := 'COGS';
Sheet.Range('G1').Value := 'Gross Profit';

Sheet.Range('B2').Value := 119903.32;
Sheet.Range('C2').Value := 1284487.9;
Sheet.Range('D2').Value := 0;
Sheet.Range('E2').Value := 1281430.5;
Sheet.Range('F2').Value := 600345.81;
Sheet.Range('G2').Value := 684142.05;

Sheet.Range('B3').Value := 88616.06;
Sheet.Range('C3').Value := 875341.22;
Sheet.Range('D3').Value := 1094876;
Sheet.Range('E3').Value := 878037.17;
Sheet.Range('F3').Value := 456889.95;
Sheet.Range('G3').Value := 418451.27;

Range:=Sheet.Range('A1:G3');
Range.Columns.AutoFit;

//Create Chart Object
ChartObjects := Sheet.ChartObjects;
ChartObject := ChartObjects.Add(Excel.CentimetersToPoints(1),
Excel.CentimetersToPoints(2),
Excel.CentimetersToPoints(18),
Excel.CentimetersToPoints(10));

Chart := ChartObject.Chart;
Chart.SetSourceData(Range,2);
Chart.HasTitle := TRUE;
Chart.ChartTitle.Text := 'Sales Performances';

//Set Title on X-Axis
Axis := Chart.Axes(1);
Axis.HasTitle(TRUE);
Axis.AxisTitle.Text := 'Year to Date';

//Set Title on Y-Axis
Axis := Chart.Axes(2);
Axis.HasTitle(TRUE);
Axis.AxisTitle.Text := '$S';

//Set Series
Series := Chart.SeriesCollection(1);
Series.ChartType := 51;
Series := Chart.SeriesCollection(2);
Series.ChartType := 51;
Series := Chart.SeriesCollection(3);
Series.ChartType := 51;
Series := Chart.SeriesCollection(4);
Series.ChartType := 65;
Series := Chart.SeriesCollection(5);
Series.ChartType := 65;
Series := Chart.SeriesCollection(6);
Series.ChartType := 65;

Excel.Visible(TRUE);
Excel.UserControl(TRUE);
CLEAR(Excel);

The Result: