User Tools

Site Tools


transformations:excelcommand

EXCEL COMMAND

Category: Workflow / External


Description

This action performs a process on an Excel file.

  • List sheets/ranges
  • Run VBA macro
  • Rename sheet
  • Delete sheet
  • Recalculate workbook


Use cases

Run VBA macro
This mode is extremely useful for running post-workflow processes such as intricate formatting of the output data, generating reports, etc.


Action settings

Setting Description
File*Enter or browse to the fully qualified path and filename of the Excel file (.xlsx or .xlsm) to perform and command on.
CommandSelect the command to run on the selected Excel file. Options: List sheets/ranges, Run VBA macro, Rename sheet, Delete sheet, or Recalculate workbook. See below for details of each command.

* Setting can be specified using a parameter.


List sheets/ranges settings

Description: Lists the sheets and/or named ranges within the workbook in a dataset.

Setting Description
ModeSelect what to list from the workbook. Options: Sheets only, Named ranges only, or Sheets and named ranges.


List sheets/ranges options

Option Description
Sheets onlyThis option creates a dataset listing the names of the sheets within the workbook in a column named Sheet name.
A second column named Hidden displays TRUE if the sheet is a hidden sheet, or FALSE if not.
Named ranges onlyThis option creates a dataset listing the names of the named ranges within the workbook in a column named
Range name. A second column named Hidden displays TRUE if the range is hidden, or FALSE if not.
Sheet and named rangesThis option creates a dataset listing the names of the sheets and named ranges within the workbook in a column
named Sheet or range name. A second column named Hidden displays TRUE if the sheet/range is hidden, or FALSE if not.


Run VBA macro settings

Description: Runs a VBA subroutine or function within the selected Excel file.

Setting Description
Macro name*Select which macro in the Excel file to run. Note: The selected file must be an .xlsm file.
Capture return valueIf the macro being run is a function that returns a value, checking this will capture the return value.
Macro arguments*If the macro being run accepts arguments, the values to be passed in are listed here. Arguments should be
listed in the order the macro is expecting them.

* Setting can be specified using a parameter.


Rename sheet settings

Description: Renames the selected sheet to a new name.

Setting Description
Current name*Select the existing sheet in the Excel file to be renamed.
New name*Enter the name the selected sheet will be changed to.

* Setting can be specified using a parameter.


Delete sheet settings

Description: Removes the selected sheet from the workbook.

Setting Description
Sheet name*Select the existing sheet in the Excel file to be deleted.

* Setting can be specified using a parameter.


Recalculate workbook settings

Description: Forces the recalculation of all formulas within the workbook.
This mode has no settings.


Remarks

Run VBA macro

When running a macro that alters the macro's own file, do not include a Workbook.Close statement at the end of the subroutine. Doing so will generate an error as EasyMorph holds the Excel file open. Remove the statement and allow EasyMorph to close the file. If using a Workbook.Close True statement to both save and close the workbook, use a Workbook.Save statement instead.

When capturing a return value from a function macro, the output dataset of the action will be the captured value. If the return value is an array, each value will be split into its own row.


See also

transformations/excelcommand.txt · Last modified: 2021/11/26 20:57 by craigt