Excel column actions DAC
The Excel column actions dynamic advanced command is part of the Excel commands. Use this command to perform actions on Excel columns, like counting columns in a row or inserting and deleting columns. 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 Excel column actions command to enhance your automation in various scenarios:
-
Counting the number of columns in a specified row.
-
Inserting columns in a worksheet at a specified location.
-
Deleting columns from a worksheet.
In the Nintex Wizard Editor, search for the Excel column actions command and drag it into your steps. Use the table below to configure each field and understand the settings:
Field | Description | What to do | |
---|---|---|---|
Select Excel file | The Excel file you want to work with. | Enter the file path (e.g., C:\Users\dev\Documents\file.xlsx), use a variable, or click Browse to select the file. | |
Unlock with password | Option to unlock a password-protected Excel file. | If the Excel file is password-protected, check this box and enter the password in the field provided. | |
Worksheet name or Worksheet position | Method of identifying the worksheet. | Specify the worksheet by name (e.g., Sheet1) or by its position (e.g., 1 for the first worksheet) within the workbook. | |
Action | |||
Get Column Count | Row index | Specifies the index of the row where the column count will take place. | Enter the row index or a variable that represents the row where the column count will take place. |
Number of empty cells allowed | Defines the maximum number of consecutive empty cells allowed before the wizard stops counting. The count ends at the last non-empty cell. | Enter the maximum number of empty cells allowed before the wizard stops counting columns. The last non-empty cell will define the column count. | |
Return result in variable | The variable to store the result. | Select a variable from the dropdown or enter a new variable name to store the result of the action. Use a descriptive name for easier identification later (e.g., ColumnCount). | |
Delete Column | From column index | Identifies the starting column index where the deletion begins. | Enter the index of the first column to delete or use a variable for this. |
Number of columns to delete | Indicates the total number of columns to delete, starting from the specified column index. | Specify how many columns to delete starting from the column index or use a variable. | |
Insert Column | Insert at column index | Specifies the column index where you insert new columns. | Enter the index where you want to insert the new column(s) or use a variable for this. |
Number of columns to insert | Defines the number of columns you will insert at the specified index. | Specify how many columns to insert at the specified column index or use a variable. | |
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. |
Follow these best practices when using the Excel column actions command:
-
Verify the worksheet name or position to select the correct one before performing any action.
-
Use meaningful variable names to make debugging and maintenance easier.
-
Always configure error handling messages for cases where the file, worksheet, or columns are not found or accessible.
This example shows how to count the number of columns in the first row of a specified worksheet in an Excel file.
-
Add the Excel column actions command to your wizard and complete the following fields:
-
Select Excel file: C:\Users\dev\Downloads\file_example_XLS_5000.xls
-
Worksheet name: Sheet1
-
Action: Select Get Column Count.
-
Row index: 1
-
Number of empty cells allowed: 5
-
Return result in variable: ColumnCount
-
Error variable: Error
-
-
Use the View variable list command to verify the command works without errors.