Table of Contents
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). |
| Mode | Select 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 range* | When checked, a named range will be created within the Excel file for the output dataset. Enter the name of the range to use. |
| Create table* | When 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 range | When checked, a named range will be created within the Excel file for the output dataset. Enter the name of the range to use. |
| Create table | When 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 |
|---|---|
| Overwrite | The new file replaces the original file. |
| Rename | The 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 execution | The 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
- Split Excel spreadsheets into multiple spreadsheets (Project; Module: Main; Group: Tab 1; Table: Table 1; Action position: 4)
- Data validation list (Project; Module: Main; Group: Tab 1; Table: Change validation list values; Action position: 2)
- Create a new table with columns based on row values (Project; Module: Module 1; Group: Tab 1; Table: Table 1; Action position: 7)