Creating Charts in Excel using Automations

rkaufmannrkaufmann Member Posts: 61

I create an Excel-Sheet with Navision using Automation-Variables to control Excel.

Creating a Excel-Sheet and fill it with data is not a problem, but now I want to create a chart based on the data in the Excel-Sheet.

I use the following Globals of type Automation:
xlApp: 'Microsoft Excel 11.0 Object Library'.Application
xlWorkbook 'Microsoft Excel 11.0 Object Library'.Workbook
xlWorksheet: 'Microsoft Excel 11.0 Object Library'.Worksheet
xlRange: 'Microsoft Excel 11.0 Object Library'.Range
xlChart: 'Microsoft Excel 11.0 Object Library'.Chart
xlChartObject: 'Microsoft Excel 11.0 Object Library'.ChartObject
xlChartObjects: 'Microsoft Excel 11.0 Object Library'.ChartObjects
xlSeriesCollection: 'Microsoft Excel 11.0 Object Library'.SeriesCollection
xlSeries: 'Microsoft Excel 11.0 Object Library'.Series

Now I use the folloing C/AL-Code to create the chart:

xlChartObjects := xlworksheet.ChartObjects; //Create an instance for automation
xlChartObject := xlChartObjects.Add(xlApp.CentimetersToPoints(15),xlApp.CentimetersToPoints(5),
xlApp.CentimetersToPoints(8),xlApp.CentimetersToPoints(9)); //Create a new chart an place it on the sheet

xlChart := xlChartObject.Chart; // Create an instance for the autoamation
xlChart.ChartType(54); // setting the chart type (54 = 3D Column Clustered)
xlSeriesCollection := xlChart.SeriesCollection; // create an instance for the automation
xlSeries := xlSeriesCollection.NewSeries; // create a new Dataseriens in the chart

Till here everything works fine.
But now I want to "tell" the new Dataseries from where to get the Data, and that's my problem. It does not work.

When recording a Macro in Excel the VBA-Code looks linke this:
ActiveChart.SeriesCollection(1).Values = "=(Tabelle1!R15C3,Tabelle1!R22C3,Tabelle1!R29C3,Tabelle1!R36C3)"

If i use C/AL Code like:

xlSeries.Values := 'C5:C29';
xlSeries.Values := '=C5:C29';
xlSeries.Values := 'Sheet1!C5:Sheet1!C29';
xlSeries.Values := '=Sheet1!C5:Sheet1!C29';

it does not work.

xlSeries.Values := '=(Tabelle1!R15C3,Tabelle1!R22C3,Tabelle1!R29C3,Tabelle1!R36C3)';
like in VBA it does not work eihter.

I always get an C/AL error, that the "Values" can not be set.

Same it I try to set the Name of Dataseries.

Has anyone any experience with this kond of problem?

Any help is welcome.



  • AlbertvhAlbertvh Member Posts: 515
    I use the following
    Range := GetColumnStr(1) + FORMAT(1) + ':' + GetColumnStr(ColumnNo) + FORMAT(RowNo - 1);
    XlRange := Sheet.Range(Range);
    Book.ActiveChart.ChartType := 51;

    I don't have excel 11 but I think the process should be the same :-k
  • rkaufmannrkaufmann Member Posts: 61

    thanks for your response.

    If you use "Book.Charts.Add", the charts is inserted on a new Sheet, isn't it?

    I need the chart on the same sheet as the Data, and I need it in a specific size and position.

    Is it possible (with your method) to add a second "DataSource" to the chart, because I need two DataSeries, which are compared to each other?
  • AlbertvhAlbertvh Member Posts: 515
    Hi Rolf,

    It will add the chart to the first worksheet.

    I have not tried to get two data ranges but maybe you can then select a second range?


  • marklynchmarklynch Member Posts: 3
    I am using Zet Excel platform
    Try it
    It helped me a lot !
  • yaconspremiumyaconspremium Member Posts: 1
    marklynch wrote: »
    I am using Zet Excel platform
    Try it
    It helped me a lot !

    huge thanks for your recommendation. do you think that is better than actual Except? is it for free?
    i will try it but was wondering if yoou could help me guiding me through its usage. thanks again.
Sign In or Register to comment.