Step-By-Step Guide to Use Excel Recorder to Calculate the Invoice Total

Let's create an example of automating the process of calculating the invoice total of the customer bill available in an excel file.

Prerequisite:

In the Trust Centre settings options of the Excel application, the Enable all macros and Trust access to the VBA project object model check boxes are selected.

1. In the Canvas Tools panel, click Excel Recorder. The ScriptStore.xlsm excel file along with the Excel Recorder interface appears.

2. Click the (Start Recording) icon to start the recording and perform the steps to record for automation.

3. In the Menu bar of the ScriptStore.xlsm excel file, click File.

4. Click Open. Browse and select the required customer bill excel file. The excel file appears.

5. Enter the text Invoice Total at the end of the rows.

6. Calculate the sum of the Total column of the items mentioned in the invoice excel and press Enter. The total amount appears.

7. Copy the cell where the invoice total is displayed.

8. Click the (Stop Recording) icon to stop the recording. The Open in Studio button appears.

9. Click Open in Studio button to return to the Canvas. An Excel Script activity is created with a link to the script of the steps recorded. It automatically creates arguments related to the operations performed in the excel file with default values. You can edit the name of the created argument.

10. Save the process.

To view the calculated invoice total in Automation Studio, let's add WriteLine activity. You can assign this process to a robot, if you want to execute the process outside Automation Studio.

11. Add a WriteLine activity below the Excel Script activity and in the Text field, enter formulaArg1 + " = " + copyExcelArg1

12. Setup the environment and then perform test run.

The Output console displays the calculated invoice total.