Excel - Run macro

Declaration

<AMEXCEL ACTIVITY="run_macro" SOURCE="text" VISIBLE="YES/NO" 
DESTINATION="text" OVERWRITE="YES/NO" MACRO="text" CELLREF="text" />

Description: Runs an existing Excel macro, which is typically a set of instructions created by recording a sequence of keyboard and mouse actions using the Excel Macro Recorder. This activity requires an interactive logon session to function properly.

IMPORTANT: Excel activities rely on Microsoft's Excel engine to perform their work,therefore, MS Excel must be licensed and installed on the system in order for these activities to function properly.

Practical Usage

NOTE: An Excel macro is an automated input sequence that is used to eliminate the need to repeat the steps of common tasks over and over. Excel users can run existing macros any time with the simple touch of a button or automatically start the macro upon the occurrence of an event or condition, greatly speeding complicated spreadsheet work.

Macro Parameters

Property

Type

Required

Default

Markup

Description

Source

Text

Yes

(Empty)

SOURCE="c:\temp\file.xlsx"

The path and filename of the Excel macro-enabled workbook (*.xlsm) that this activity should run. Click the folder icon to navigate to the file using an Open File dialog.

Application is invisible

Yes/No

No

No

VISIBLE="NO"

If set to YES, specifies that the Excel window will not be displayed during runtime and all processing will occur in the background. This option should be set to NO during task construction and debugging but can be set to YES for production. Set to NO by default.

Destination

Text

Yes

(Empty)

DESTINATION="c:\temp\file.xlsx"

The path and filename in which to save the macro processed workbook upon completion. Click the folder icon to navigate to the destination using an Open File dialog.

Overwrite if workbook already exists

Yes/No

No

No

OVERWRITE="YES"

If set to YES, the newly created workbook will overwrite an existing workbook with the same name, If set to NO, an error will occur during runtime as a result of a matching filename. This parameter is set to NO by default.

Macro name

Text

Yes

(Empty)

MACRO="theMacro"

The name of the Excel macro to automate.

Cell reference

Text

No

(Empty)

CELLREF="text"

The cell address that identifies a cell on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. This parameter uses the A1 reference style, which refers a cell using the column letter followed by the row number (e.g., column A, row 10 = A10).

Use active worksheet

---

---

---

---

If enabled, specifies that the macro will run on the currently active worksheet. If this parameter is enabled, the Use specific worksheet parameter is ignored. This is a design-time parameter, therefore, contains no markups.

Use specific worksheet

Text

No

Save

WORKSHEET="Sheet1"

If enabled, specifies the name of a specific worksheet in which to run the macro. If this parameter is enabled, the Use active worksheet parameter is ignored.

The sample AML code below can be copied and pasted directly into the Steps panel of the Task Builder.

Open Excel workbook "c:\temp\test_macro.xlsx". Use worksheet "Sheet1". Activate cell "A10". Run macro "test". Save as Excel file "c:\temp\macro_result.xlsx".

<AMEXCEL ACTIVITY="run_macro" SOURCE="c:\temp\test_macro.xlsx" 
VISIBLE="NO" DESTINATION="c:\temp\macro_result.xlsx" OVERWRITE="YES" 
 MACRO="test" CELLREF="A10" WORKSHEET="Sheet1" />