User Tools

Site Tools


transformations:excelcommand

EXCEL COMMAND

Category: Workflow / External


Description

This action performs a command on an Excel file.

  • List sheets/ranges
  • Run VBA macro
  • Rename sheet
  • Delete sheet
  • Recalculate workbook
  • Re-save
  • Save as PDF


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, .xslm, .xlsb, or .xls) 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 or the first value of a column.


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.
If macro failsSelect how EasyMorph behaves if the Excel macro fails to run. Options: Fail (display an error and stop the workflow) or Warn (display a warning and continue the workflow).

* Setting can be specified using a parameter or the first value of a column.


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 or the first value of a column.


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 or the first value of a column.


Recalculate workbook settings

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


Re-save settings

Description: Forces a re-saving of the .xlsx workbook to correct possible format problems introduced programmatically by 3rd-party utilities/libraries.
This mode has no settings.


Save as PDF settings

Description: Generates a PDF file from the selected sheets in an Excel workbook.

Setting Description
PDF file*Enter the fully qualified path and filename of the PDF file to be generated.
If the PDF file already existsRefer to the File already exists options table below for details.
Page rangeChoose which pages to include in the PDF file. Options: All pages (all pages in the select sheet will be exported to the PDF file), or Pages (and enter the starting and ending number of the pages to be exported).
SheetsChoose which workbook sheets will be exported to the PDF file. Options: All sheets (the content from all worksheets will be exported), or Selected sheets (select the sheets to include from the list).

* Setting can be specified using a parameter or the first value of a column.


File already exists options

Option Description
OverwriteThe new file replaces the original file.
RenameThe original file will be renamed with "backup" and a serial number appended to the file name. The new file will
possess the name specified in the File setting.
Halt project executionThe project will stop processing and this action will display a warning symbol.


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.

Save to PDF

This mode triggers the "Export to PDF" process within Excel which uses the "Microsoft Print to PDF" printer to format the pages. Pre-select this printer in Excel prior to laying out the pages to be printed to ensure margins and spacing are accurate in the resulting PDF files.


See also

transformations/excelcommand.txt · Last modified: 2023/04/20 19:36 by craigt

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki