Insert/Delete Columns

Common

• DisplayName - The display name of the activity.

Destination

• NoColumns - The number of columns you wish to add or remove. This field supports only integers or Int32 variables.
• Position - The column where the insertion or removal begins. This field supports only integers or Int32 variables.

Input

• ChangeMode - Selects whether the activity adds or removes columns. Selecting Add will add columns to the document, while selecting Remove will remove them.
• SheetName - The name of the sheet in the workbook where the change must be made. This field supports only strings and String variables.

Misc

• Private - If selected, the values of variables and arguments are no longer logged at Verbose level.

1. Open Studio and create a new Process named by default Main.

2. Drag a Sequence container in the Workflow Designer and create the following variable:

   Variable Name	Variable Type	Default Value
   NewFile	GenericValue	N/A

3. Drag an Assign activity inside the sequence container.

   • Add the variable NewFile in the To field and the expression DateTime.Now.ToString("yyyy'-'MM'-'dd'T'HH''mm''ss")+".xls" in the Value field.

4. Place a Copy File activity below the Assign activity.

   • In the Properties panel, add the value "Interest-Rates.xls" in the Path field and the variable NewFile in the Destination field.

5. Drag an Excel Application Scope under the Copy File activity.

   • Add the variable NewFile in the WorkbookPath field.
   • In the Properties panel, select the check boxes for the AutoSave, CreateNewFile and Visible options. The robot is now allowed to create a new Excel workbook, to automatically save all the changes made to it and to open the Excel file in the foreground while performing actions on it.

6. Drag an Excel Delete/Insert Columns activity inside the Do container.

   • In the Properties panel, add the value 6 in the NoColumns field. This represents how many columns are deleted.
   • Add the value 5 in the Position field. This represents the column from which the deleting process begins.
   • Select the Remove option from the ChangeMode drop-down list.
   • Add the expression "Short_term_Interest_Rates" in the SheetName field.

7. Drag another Excel Delete/Insert Columns activity and place it below the first one.

   • In the Properties panel, add the value 1 in the NoColumns field. This represents how many columns are inserted.
   • Add the value 5 in the Position field. This represents the column from which the inserting process begins.
   • Select the Add option from the ChangeMode drop-down list.
   • Add the expression "Short_term_Interest_Rates" in the SheetName field.
8. Add an Excel Write Cell activity under the Delete/Insert Columns activities. This creates a new column with the name Average.
   • In the Properties panel, add the expression "E4:E4" in the Range field.
   • Add the name "Short_term_Interest_Rates" in the SheetName field.
   • Add the expression "Average" in the Value field.

9. Add another Excel Write Cell activity right below the first one. This activity calculates the average interest rates.

   • In the Properties panel, add the expression "E5:E35" in the Range field.
   • Add the name "Short_term_Interest_Rates" in the SheetName field.
   • Add the expression "=AVERAGE(F5:P5)" in the Value field.

10. Run the process. The automation process creates a new workbook and manipulates the data from the original one.

    Download example