Calls method from Excel.Application object.
Syntax
|
CHGVAR |
VAR(&CMD) VALUE('XLMETHOD') |
|
CHGVAR |
VAR(&PARM1) VALUE('Method') |
|
CHGVAR |
VAR(&PARM2) VALUE(' ') |
|
CALL |
PGM(LNCCMD) PARM(&HANDLE &CMD &OPT &PARM1 + &PARM2 &RESULT)) |
|
MONMSG |
MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR)) |
Parameters
|
Parameters |
|
|
Parm1 |
Full path of the method to call. |
|
|
Note
The XLMETHOD command is used to execute methods of the Excel VBA language from the AS/400.
The contents of Parm1 respect the syntax used in Visual Basic.
It is possible to use all the constants of Excel and Visual Basic Application or the value of the constant itself.
To get an idea of the methods to call, go to Word in "Macro Recording" 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 ("Chart 1"). Activate
ActiveChart.ApplyDataLabels Type: = xlDataLabelsShowPercent, LegendKey: = True _
, HasLeaderLines: = False
Note: There are 2 differences to note between the VB syntax of Excel and the 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:
ActiveSheet.ChartObjects("Chart 1")
becomes
ActiveSheet.ChartObjects.Item("Chart 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: = True
becomes
ActiveChart.ApplyDataLabels(xlDataLabelsShowPercent; True)
Otherwise, choose to write a macro in Excel, which you will call by XLEXEMACRO.
Example
Below example activates the sheet 2 of Excel
|
CHGVAR |
VAR(&CMD) VALUE('XLMETHOD') |
|
CHGVAR |
VAR(&PARM1) VALUE('Sheets.Item("Sheet2").Activate') |
|
CHGVAR |
VAR(&PARM2) VALUE(' ') |
|
CALL |
PGM(LNCCMD) PARM(&HANDLE &CMD &OPT &PARM1 + &PARM2 &RESULT)) |
See also