Tutorial: Adding Your Own Formulas to the Project Notebook

The default Project Notebook available with all automation projects comes with a number of formulas that can be easily incorporated in your automations when selected from the menu of activity fields. In this tutorial, we will create a simple currency conversion automation that demonstrates how you can add a formula of your own to the Project Notebook and use it in your project.

We will start by manually adding a formula to convert USD to EUR in cell C1 of the Scratchpad sheet in the Project Notebook, and then we will create an automation that performs the following tasks:

Retrieves the US Dollar to EUR exchange rate from a web search and adds it in cell A1.
Prompts for an amount in USD during project execution and adds the amount in cell B1.
Displays a message box with the EUR equivalent of the USD amount calculated in cell C1.

To wrap things up, we will future-proof our automation against any changes to the Project Notebook by naming the cells used in the automation and updating the project to use the named cells.

Step 1: Set up a project and update the Project Notebook.
1. Create a new blank project using the default settings .
2. Click Project Notebook in the StudioX ribbon to open the file in Excel.
3. In the Excel file, select the Scratchpad sheet, and then enter the formula =A1*B1 in cell C1. This formula converts the USD amount added in cell B1 to EUR using the exchange rate added in cell A1.
Step 2: Get the exchange rate from the web.
1. Open Internet Explorer, navigate to google.com , and search for usd to euro exchange rate.
2. In StudioX, click in the Designer panel, and then find the Use Application/Browser activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
3. In the Use Application/Browser activity, click Indicate Application, then move the mouse to the page with the search results for the exchange rate and, when the browser page is highlighted in blue, click anywhere inside it.
  
  You have indicated that you want to use the page with the results of the search in your automation.
4. Click inside the Use Application/Browser activity, and then find the Get Text activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
5. In the Get Text activity:
  - Click Indicate target on screen, then move the mouse pointer on the Euro value at the top of the web search results page, and, when it is highlighted, click the value. The value is identified as the target and highlighted in green. Click Confirm in the Selection Options window to save the selection. An image of the target and any identified anchor is displayed in the Get Text activity.
  - Click on the right side of the Save to field, and then select Project Notebook (Notes) > Indicate in Excel. In Excel, select cell A1, and then click Confirm in the UiPath tab in the Excel ribbon. You have indicated that you want to paste the exchange rate in cell A1 in the Scratchpad sheet of the Project Notebook.
Step 3: Prompt for an amount in USD and display its equivalent in EUR.
1. Click below the Get Text activity, and then find the Write Cell activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
2. In the Write Cell activity:
  - Click on the right side of the What to write field, and then select Ask when run.
  - Click on the right side of the Where to write field, and then select Project Notebook (Notes) > Indicate in Excel. In Excel, select cell B1, and then click Confirm in the UiPath tab in the Excel ribbon.
    
    You have indicated that you want to prompt the user for an amount in USD during project execution and that you want to paste this value in cell B1 in the Scratchpad sheet of the Project Notebook.
3. Click below the Write Text activity, and then find the Message Box activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
4. In the Message Box activity, click on the right side of the Message field, and then select Text.
5. We want to display the message "X USD is the equivalent of Y EUR". To achieve this, in the Text Builder:
  - Click on the right side of the text box, and then select Project Notebook (Notes) > Indicate in Excel. In Excel, select cell C1, and then click Confirm in the UiPath tab in the Excel ribbon..
  - Enter the text USD is the equivalent of.
  - Click on the right side of the text box, and then select Project Notebook (Notes) > Indicate in Excel. In Excel, select cell B1, and then click Confirm in the UiPath tab in the Excel ribbon..
  - Enter the text EUR..
    
    The text should look like in the following image.
6. Click Save in the Text Builder, then click Save in the StudioX ribbon to save the automation, and click Run to execute the automation.
  
  The exchange rate is retrieved from the web search, the user is prompted for a value, the value is converted from USD to EUR in Excel, and a message is displayed with the result of the conversion.
Step 4: Optimize your automation by naming the cells in the Project Notebook.
Your automation is now fully functional, but you can further improve it by naming the cells used in it and updating the activities that reference them. Naming cells has the following advantages:
- When configuring activities, you can select named cells directly from the menu, without having to open the file and indicate in Excel.
- Identifying cells by name rather than by reference means that you don't have to worry about updating activities to point to the right cells if changes to the Excel file result in cells being shifted.
To name the cells and update the project:
1. Open the Project Notebook in Excel and rename the following cells in the Scratchpad sheet using the Name Box at the left end of the Formula bar: for cell A1 enter the name ExchangeRate. for cell B1 enter the name USDValue. and for cell C1 enter the name EURValue.
2. Update the activities in the project to use the cell names:
  - In the Get Text activity, click on the right side of the Save to field, and then select Project Notebook (Notes) > Scratchpad [Sheet] > ExchangeRate.
  - In the Write Cell activity, click on the right side of the Where to write field, and then select Project Notebook (Notes) > Scratchpad [Sheet] > USDValue.
  - In the Message Box activity, click , then select Text and update the two references to cells in Excel:
    - Delete [Excel] Scratchpad!B1, then click on the right side of the text box and select Project Notebook (Notes) > Scratchpad [Sheet] > USDValue.
    - Delete [Excel] Scratchpad!C1, then click on the right side of the text box and select Project Notebook (Notes) > Scratchpad [Sheet] > EURValue.
The updated project should look like in the following image.

Download example