Excel commands
Excel commands are dynamic advanced commands designed to interact with Excel files, enabling you to automate various Excel-related tasks. These commands allow you to manipulate data within Excel files by copying, pasting, deleting, or even running macros. While most commands do not require Excel to be installed, you will need it for specific commands like Run macro or when selecting ranges in certain actions. Using Excel commands in your automation workflows enhances efficiency when handling spreadsheets.
Before you begin:
Learn how to navigate the Nintex Wizard Editor of the Studio.
Understand how to create wizards and use advanced commands.
Understand variables.
Learn how to customize error handling within an advanced command.
Understand how to configure wizard fallbacks.
-
Nintex Studio access
-
A wizard created in the Wizard Catalog
-
Dynamic advanced commands installed
See the table below for an overview of each command and its functions. For more details on how to set up each command, check the individual command documentation.
Command | Description |
---|---|
Convert a worksheet from an Excel file to CSV format and save it to the specified location. |
|
Copy the selected cell values from an Excel file and store them in a variable. |
|
Create a new Excel file in the chosen location with your specified file name. |
|
Delete the selected cell values from an Excel file. |
|
|
Get the number of columns in a specific row and store it in a variable, or insert or delete columns. |
Get the number of rows in a specific column and store it in a variable, or insert or delete rows. |
|
|
Get details about the worksheets in an Excel file, or rename, move, or delete worksheets. |
|
Paste values or formulas into an existing Excel file. |
Run an SQL query on an Excel worksheet to return specific values from a range or the whole sheet. |
|
|
Run a VBA (Visual Basic for Applications) macro on the selected Excel file. This command requires Excel to be installed. |
Before using Excel commands, review these limitations and best practices to ensure optimal performance and avoid potential issues.
File format compatibility
The Excel commands support .xlsx, .xlsm, .xlsb, .xls, .csv, and .tsv formats, but each format has specific limitations:
-
XLSX: This is the preferred format for most Excel files, supporting all modern features, including formulas, pivot tables, and charts. Note that using very large or complex .xlsx files (with extensive formulas or formatting) may lead to memory or performance issues.
-
XLSM: Use this format when macros are needed, as it supports embedded VBA macros, which are scripts used to automate repetitive tasks in Excel. Macros cannot be created or edited by the tool—only preserved. This format has similar performance to .xlsx but with a slight overhead due to storing macros.
The Run macro command only works with .xlsm files. -
XLSB: This binary format is optimized for speed and file size, often reading and writing faster than .xlsx, especially for large datasets. Use .xlsb when performance and storage efficiency are key factors. Note that .xlsb is not supported in the Create new Excel file DAC. Some third-party tools may also have limited support for this format.
-
XLS: This older format (used by Excel 97-2003) is limited to 65,536 rows and 256 columns per sheet. It may use more memory and process data slower than newer formats, especially for large files.
-
CSV/TSV: These are simple file formats that store data as plain text and are limited to a single sheet. They don’t support advanced Excel features, such as cell formatting (like bold text or cell color), formulas, or multiple sheets.
Performance with large data
To maintain smooth performance:
-
Keep files under 200 MB and use fewer than 100,000 rows or 500 columns, especially for .xlsx files with complex data.
-
Extensive formulas in large files can significantly slow down processing.
-
Use .xlsb for very large files, as it often reads and writes faster than .xlsx.
Additional considerations
-
Large Excel files with complex features (e.g., pivot tables, charts, or conditional formatting) can consume significant memory and impact system performance. Avoid using files larger than 500 MB, as they may slow down operations and increase the risk of memory errors.
-
If multiple users or processes access the same Excel file, commands may encounter locking issues or errors.
-
The Copy from Excel and Paste to Excel commands do not keep cell formatting (e.g., currency or date formats). While pasted values may be recognized as a data type, formatting will reset to default. To keep specific formats, apply them after pasting.
-
Excel must be installed if using the Run macro command or when selecting ranges in the Copy from Excel and Paste to Excel commands.
-
Ensure correct file paths and variable names to avoid errors when performing actions on Excel files.
-
For more information on Excel’s built-in specifications and limitations (e.g., maximum row and column counts, file size restrictions), visit Excel Specifications and Limits.