XLDRAWGR command

Creates a graph in a new sheet.

Syntax

CHGVAR	VAR(&CMD) VALUE('XLDRAWGR')
CHGVAR	VAR(&PARM1) VALUE('selection')
CHGVAR	VAR(&PARM2) VALUE(' ''TYPE'' ''[ROWS\|COLS]'' + ''Nb_Categorie'' ''Nb_Serie'' ''Name'' ')
CALL	PGM(LNCCMD) PARM(&HANDLE &CMD &OPT &PARM1 + &PARM2 &RESULT)

Parameters

Parm1

Selection of cells containing the data or the data labels (co-ordinates separated by colons ":"). If several cells are specified, it is necessary to separate them with a semicolon (;) .
The numbers of characters used to specify selection has to be less than 260. Excel area has to be used to limit number of characters.

Parm2

Other parameters

This command can accept the following parameters :

- Type of graph (AREA, BAR, COLUMN, LINE, RADAR, DONUT, XYSCATTER, 2DPIE, 3DAREA, 3DBAR, 3DCOLUMN, 3DLINE, 3DPIE).

- Arrangement of data (ROWS or COLS).

- Number of lines or columns of the source range containing the categories.

- Number of lines or columns of the source range containing the series.

- Title of the graph.

Example 1

Create un graph from the following Excel table :

	A	B	C
1	Années	ITEMSTOTAL	AMOUNTPAID
2	2001	162 730,05 €	162 730,05
3	2002	315 910,20 €	273 096,75
4	2003	162 379,40 €	162 379,40
5	2004	953 405,35 €	786 008,75
6	2005	967 225,30 €	935 238,30
7	2006	360 950,80 €
8	2007	65,00 €

PGM

DCL VAR(&HANDLE) TYPE(*CHAR) LEN(50)

DCL VAR(&CMD) TYPE(*CHAR) LEN(10)

DCL VAR(&OPT) TYPE(*CHAR) LEN(1)

DCL VAR(&PARM1) TYPE(*CHAR) LEN(512)

DCL VAR(&PARM2) TYPE(*CHAR) LEN(1024)

DCL VAR(&RESULT) TYPE(*CHAR) LEN(512)

LNCOPEN

LNCCMD CMD(EXCELOPEN)

LNCCMD CMD(XLOPENFILE) +

PARM1('%LNCDIR%\samples\test.xls') +

PARM2(visible)

LNCCMD CMD(XLDRAWGR) PARM1('A1:C8') +

PARM2('''COLUMN'' ''COLS'' ''1'' ''1'' ''TEST1''')

LNCCMD CMD(EXCELSHOW)

LNCCMD CMD(END)

SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) +

MSGDTA(&RESULT) MSGTYPE(*INFO)

LNCCLOSE

ENDPGM

Exemple 2

PGM

LNCOPEN

LNCCMD CMD(EXCELOPEN)

LNCCMD CMD(XLOPENFILE) +

PARM1('%LNCDIR%\samples\test.xlsx')

LNCCMD CMD(XLDRAWGR) PARM1('Sheet1!$A$1:C$4') +

PARM2('''COLUMN'' ''COLS'' ''1'' ''1'' +

''TEST1''')

LNCCMD CMD(XLMETHOD) +

PARM1('Sheets.Item("Chart1").Select')

LNCCMD CMD(EXCELSHOW)

LNCCLOSE

ENDPGM

Exemple 3: several cells used to define selection (limit of 260 characters)

LNCCMD CMD(XLDRAWGR) +

PARM1('Sheet1!$A$1;Sheet1!$A$2;Sheet1!$A$3;+

Sheet1!$A$4;Sheet1!$A$5;Sheet1!$A$6;Sheet1!+

$A$7;Sheet1!$A$8;Sheet1!$B$1;Sheet1!$B$2;Sh+

eet1!$B$3;Sheet1!$B$4;Sheet1!$B$5;Sheet1!$B+

$6;Sheet1!$B$7;Sheet1!$B$8;Sheet1!$C1;Sheet+

1!$C$2;Sheet1!$C$3') PARM2('''COLUMN'' +

''COLS'' ''1'' ''1'' ''TEST1''')

Exemple 4: use "data" area

LNCCMD CMD(XLDRAWGR) PARM1('Sheet1!data') +

PARM2('''COLUMN'' ''COLS'' ''1'' ''1'' +

''TEST1''')

Exemple 5 :

CHGVAR VAR(&CMD)VALUE('XLDRAWGR')

CHGVAR VAR(&PARM1)VALUE(' A2:D3 ')

CHGVAR VAR(&PARM2) +

VALUE(' ''3DPIE'' ''ROWS'' ''1'' ''0'' ''Market'' ')

CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +

&PARM1 &PARM2 &RESULT)

Note

Graphing functions in LAUNCHER are limited with XLDRAWGR.

To modify some properties of the graph, you can use the command XLMETHOD which allows to execute from the AS/400, Excel VBA methods :

LNCCMD CMD(XLMETHOD) PARM1('ActiveSheet.ChartObjects.Item("Graphique 1").Activate')

LNCCMD CMD(XLMETHOD) PARM1('ActiveChart.ApplyDataLabels(xlDataLabelsShowValue)')

To get an idea of the methods to call, go to Excel in "Macro saving" mode: "Tools" menu - "Macro" - "New macro".

Do the desired operations on the keyboard and mouse.

Stop the macro recording, and see the code generated by Excel: Menu "Tools" - "Macro" - "Macros" - "Edit"

Example :

The macro that activates the chart and modifies a property is as follows:

ActiveSheet.ChartObjects("Graphique 1").Activate

ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowPercent, LegendKey:=True _

, HasLeaderLines:= False

Note : There are 2 differences to note between Excel VB syntax and LAUNCHER syntax:

1) When selecting an object from a collection, add ".Item" after the name of the collection.
To select "Graph 1" in the graphics collection, the VB syntax is :

ActiveSheet .ChartObjects("Graphique 1")

It becomes:

ActiveSheet .ChartObjects.Item("Graphique 1")

2) The parameter names (Name:= value) are specific to the VB syntax.

With LAUNCHER, it is necessary to list the values of each parameter, in the expected order, separated by ';'.

The following statement

ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowPercent, LegendKey:=

Becomes :

ActiveChart.ApplyDataLabels(xlDataLabelsShowPercent;True)

Otherwise, choose to write a macro in Excel, which you will call by XLEXEMACRO.