Creating Charts in Excel using Automations

rkaufmann

Hi,

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.

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

Thanks,
Rolf

Albertvh

I use the following

Range := GetColumnStr(1) + FORMAT(1) + ':' + GetColumnStr(ColumnNo) + FORMAT(RowNo - 1);
Sheet.Range(Range).Select;
XlRange := Sheet.Range(Range);
Book.Charts.Add;
Book.ActiveChart.ChartType := 51;
Book.ActiveChart.SetSourceData(XlRange,2);

I don't have excel 11 but I think the process should be the same :-k

rkaufmann

Hi,

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?

Albertvh

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?

Regards

Albert

marklynch

Hello
I am using Zet Excel platform
Try it
It helped me a lot !

yaconspremium

marklynch wrote: »

Hello
I am using Zet Excel platform Trenbolone for sale
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.