Paste to Excel
The Paste to Excel dynamic advanced command is part of the Excel commands. Use this command to paste values or formulas into an existing Excel file. You can paste either a table (multiple values) or a single value. You don’t need Excel installed to use this command. Drag it into a wizard from the Advanced commands view in the Nintex Wizard Editor to use it.
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.
Use the Paste to Excel command to enhance your automation in various scenarios:
-
Paste a table of data, such as multiple rows from an application, into an Excel worksheet.
-
Paste a single value or formula across multiple cells in Excel.
-
Automatically create a new Excel file if one does not exist at the specified location.
In the Nintex Wizard Editor, search for the Paste to Excel command and drag it into your steps. Use the table below to configure each field and understand the settings:
Field | Description | What to do |
---|---|---|
What to paste | Option to paste a table or a single value. | Select either Paste a table or Paste a single value. |
Paste the contents of this variable | Defines the variable that contains the data to paste. | Select the variable containing the table or single value. If you choose Paste a table, enter the Column delimiter and Row delimiter (or use variables) to separate columns and rows. These options are unavailable if you select Paste a single value. |
How to paste | Option to paste data as static values or formulas. | Select Actual Values to paste raw data, or Formulas to paste data as formulas if present. |
Where to paste | The Excel file where you want to paste the data. You can reference it using a variable, enter the full file path, or browse for the file on your local machine. | Enter the full file path (e.g., C:\Users\dev\Downloads\sample_invoices.xlsx), use a variable, or click Browse to select the file from your computer. |
Password | Unlocks password-protected Excel files. | If the Excel file is password-protected, enter the password in this field. |
Create a new file if not exist | Option to create a new file in the specified location if one does not already exist. This is selected by default. | Select this box if you want the wizard to create a new file in the target location when the specified file is not found. If not selected, the wizard will not create a new file. |
Select in Excel | Optional feature to manually select the cells within Excel (requires Excel installation). This feature is unavailable until you fill in the required fields. | Click this button to open Excel and then select the worksheet and cells manually. This is useful when you need exact cell locations. |
Worksheet | The worksheet within the Excel file where you want to paste the data. | Enter the worksheet name (e.g., Sheet1). |
Start pasting here: Paste a table options | ||
Cell address | Defines the starting cell for pasting data. | Enter a cell address (e.g., A2) or use a variable to specify the starting cell. |
Column/Row | Specifies both the column and row for data to start in a precise location. | Enter column and row values, or use variables. |
Column | Allows pasting data starting at the first empty cell in a specific column. | Enter a column value, and the wizard will detect the first empty cell in that column automatically. |
Start pasting here: Paste a single value options | ||
Range | Defines a range of cells for pasting. | Enter a range (e.g., A2:C20) or use a variable for the range. |
Column/Row | Specifies the starting column and row, and an optional ending column and row. | Enter values for Column and Row as the starting point. Optionally, specify To Column and Row to paste the same value across a range of cells. |
Error handling | ||
Error variable | Optional error handling with customizable error messages. | Enter an Error variable name to ensure errors will display in the View variable list command. Use the default error handling messages or customize the error message and response as needed. |
Enable timeout monitoring | Optional feature to track the command's runtime and raise an error if it exceeds a set duration. | Select the checkbox to enable timeout monitoring and then set the duration (default is 1800 seconds). Customize the timeout error message, or use the default one. |
When working with Excel commands like Copy from Excel, Paste to Excel, or Delete from Excel, there are two options for selecting the cells to use: manual selection and Select in Excel. This allows you to specify which worksheet and cell range to manipulate, making the commands flexible for different use cases.
Enter Excel data selection manually
This method allows you to manually define the worksheet content to use in your command. It's especially useful when you want to use variables to select specific cells dynamically. When using variables in any of the fields, remember to enclose the variable name in dollar signs (e.g., $MyVar$).
You can choose one of the following methods for manual selection:
-
Range: Specify the range using standard Excel notation, even if it's a single cell (e.g., A1:A1), to select specific cells.
-
from/To: Specify the starting and ending column and row. For example, from: C5, To: E10, or leave the To field blank to copy all rows/columns until the wizard detects 20 consecutive empty cells.
-
Entire worksheet content: This option allows you to copy, paste, or delete the entire worksheet content. The wizard will stop after detecting 20 consecutive empty rows or columns.
Select in Excel
This method allows you to select the cells you want to use from within the Excel sheet. It's useful when you prefer to work directly in the file and see the data you're manipulating. Here's how it works:
-
Click Select in Excel to bring up the Select from Excel window. The selected Excel file appears behind the selector window.
-
Select your worksheet and cells by clicking in the Excel file to choose the worksheet (tab) and the cells you want to use in your command. The Current selection fields in the selector window fill in with your selections.
If the selector window interferes with your view, you can drag it to a different location on your screen. -
Click OK to confirm your selection. The Worksheet and Range fields in the command fill in based on your selections.
If you need to change your selections, you can either edit the fields manually or repeat the Select in Excel process.
Follow these best practices when using the Paste to Excel command:
-
Use meaningful variable names for tables and values to make debugging easier.
-
Always configure error handling to manage scenarios where the file or worksheet is unavailable.
-
Paste both regular values and formulas to make sure they appear correctly. If you’re pasting formulas, this step helps confirm that they update automatically based on other data in the Excel sheet.
This example shows how to paste a single value into a specific worksheet cell in an existing Excel file.
-
Add the Set value command to your wizard to define the value for TotalSales:
-
In the variable: TotalSales
-
Set the value: 5000
-
-
Add the Paste to Excel command and complete the following fields:
-
What to paste: Select Paste a single value.
-
Paste the contents of this variable: $TotalSales$
-
How to paste: Select Actual Values.
-
Where to paste: Click Browse and select the Excel file (sample_invoices.xlsx).
-
Worksheet: Sheet1
-
Start pasting here: Select Column/Row and enter Column: B Row: 2.
-
Error variable: Error
-
-
Add the View variable list command to verify the command works without errors.