User Tools

Site Tools


transformations:exportexcel

EXPORT INTO EXCEL SPREADSHEET

Category: Export / File


Description

This action exports the current dataset as a new Excel workbook (.xlsx) file, or into and existing Excel file, and supports the following modes:

  • Create new file
  • Create new sheet in existing file
  • Replace sheet/range in existing file
  • Replace table in existing file


Use cases

You can subdivide a dataset and export it to a multi-tabbed Excel file using iterations and passing the sheet names in using Parameters in the called module.

In conjunction with the Excel command action, it is possible to generate reports by dynamically updating the source data tables for charts and pivottables in an Excel file, and output to PDF fileS.


Action settings

Setting Description
File*Enter a fully-qualified file name for the output file (includes relative or absolute path).
ModeSelect whether this action will create a new file, create a new sheet in an existing file,
or replace a sheet or range in an existing file . See the table below for details.

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


Create new file settings

Description: Create a new Excel file or overwrite an existing file with the same name. See the File already exists options table, below, for additional details.

Setting Description
Sheet name*Enter the name of the worksheet to output the dataset to.
Create named rangeWhen checked, a named range will be created within the Excel file for the output dataset. Enter the name of the
range to use.
Create tableWhen checked, the dataset will be output into a formatted Excel table. Enter the name of the table to use.

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


Create new sheet in existing file settings

Description: This option will export the dataset to a new sheet inserted into the existing selected file.

Setting Description
Sheet name*Enter the name of the worksheet to output the dataset to.
Create named rangeWhen checked, a named range will be created within the Excel file for the output dataset. Enter the name of the
range to use.
Create tableWhen checked, the dataset will be output into a formatted Excel table. Enter the name of the table to use.

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


Replace sheet/range in existing file settings

Description: This mode will replace an existing sheet in an existing Excel workbook.

Setting Description
Sheet or range*Select the sheet or named range to replace. See Remarks, below, for additional details.

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


Replace table in existing file settings

Description: This mode will replace an existing table in an existing Excel workbook.

Setting Description
Sheet name*Select the sheet to replace the table in. See Remarks, below, for additional details.

* 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

Close target workbooks before running

When creating a new sheet or replacing a sheet/range, or overwriting an existing file, the target file must be closed prior to running this action or it will result in a warning and no content will be written. Close the target file and rerun this action to complete it successfully.

Worksheet positioning

When creating a new sheet in an existing file, the new sheet will appear in the rightmost position. A sheet being replaced in an existing file will retain its original position.

Replacing ranges and tables

When replacing an existing range or table, the content of the target Sheet will be cleared before the dataset is exported and named with the original range/table name. New ranges/tables will always be positioned on the sheet starting at cell A1.

An error will occur if replacing an existing table on a Sheet containing more than one table.


Community examples


See also

transformations/exportexcel.txt · Last modified: 2022/01/31 14:21 by craigt