Skip to Content

Build Your First Automation Using Excel SDK of SAP Build Process Automation

In this tutorial, you will be developing basic automations using Excel SDK. After completing this tutorial, you will be able to read Excel data for a given range using Excel Cloud Link, or by using a traditional approach. In this tutorial, you will also learn, how you can apply filters on the Excel data, copy the filtered data onto a new Excel sheet and save the file with a new name.
You will learn
  • How to use Excel SDK of SAP Build Process Automation to read and process data in an Excel.
r3kskRamakrishnan RaghuramanDecember 11, 2022
Created by
celineaudinsap
November 20, 2022
Contributors
celineaudinsap
pbujnicka

Prerequisites

SAP Build Process Automation has native integration to several Microsoft Office products including Outlook, Excel SDK. In this tutorial, you will explore how to use Excel SDK into your automations.

  • Step 1

    The Excel SDK is a collection of activities allowing you to create automations using Microsoft Excel. It comes with a rich set of functionalities such as:

    • Create new Excel Workbook
    • Create new Excel Worksheet
    • Read and write data from an Excel sheet, for a given data range
    • Apply Filter on a column
    • etc

    To design your automation, you will need an Excel file filled with the sales orders data. You have the possibility to create it yourself using the below sample data:

    Order NumberOrder Amount Order Date Shipping Country Expected Delivery Date Order Status
    PO7991 410418.22 1/21/2022 United States of America 1/29/2022 In Time
    PO7918 150935.13 1/22/2022 United Kingdom 1/27/2022 Urgent
    PO7375 313977.82 1/23/2022 United Kingdom 2/20/2022 In Time
    PO7311 755055.4 1/24/2022 United Kingdom 3/30/2022 In Time
    PO6858 429358.4 1/25/2022 United Kingdom 2/20/2022 In Time
    PO6368 43739.82 1/26/2022 United Kingdom 3/25/2022 In Time
    PO6189 483574.12 1/27/2022 Germany 2/5/2022 In Time
    PO3115 2739956 1/28/2022 Germany 3/10/2022 In Time
    PO2686 220887.56 1/29/2022 Germany 3/5/2022 In Time
    PO8282 436955.64 1/30/2022 United States of America 3/30/2022 In Time

    You can also use an Excel sample of your choice.

  • Step 2
    1. From the SAP Build Process Automation Lobby, create a new automation.
    2. Provide an appropriate name to your project and relevant automations.
    3. Select your active desktop agent version to be used during design and development of your automation.
    4. By default Excel and Core SDK are added to the automations and your automation opens in a new browser tab. You have a couple of ways to read Excel Data:
      • Using the activity Excel Cloud Link to achieve Excel Data Mapping.
      • Traditional step by step approach to read / write Excel data.

      In this tutorial, you will explore both ways.

      What is Excel Data Mapping?

      Excel Data Mapping allows you to transform columns-based data from an Excel sheet into datatype that can be used in your automation. The data from the Excel sheet stays the same but the structure becomes a data type structure, making it possible to use throughout your project.

    5. As a first step, locate and add Open Excel Instance activity to the automation flow.

      Add Open Excel Instance

    6. Similarly locate and add Excel Cloud Link from the activity pane.

      Add Excel Cloud Link

    7. Now edit the properties of Excel Cloud Link activity. In the flow chart, select the Excel Cloud Link activity, on the right side menu, choose Edit Activity button.

      Edit Excel Cloud Activity

    8. Within the edit activity screen, you can either use the file picker or drag and drop your test Excel.

      Import Excel

    9. As you can see, with the Excel cloud link capabilities, it already pre-selects the first sheet, it groups data by column names, it also finds out in few seconds the complete range of the Excel.

      Browse the data

    10. While Excel cloud link is great in processing the data, it still needs for runtime reference, the path of your Excel. Typically, you need the full path of the Excel. i.e. folder path + file name. Please provide your full path location into the path field.

      Provide Excel path

    11. SAP Build Process Automation has data type functionality to logically group and process your data. You can create a data type out of the added Excel by using the Excel data structure (2).

      Create Data type out of Excel

    12. Provide a data type name. As you can see Excel Cloud Link already identifies field names using the first row of each column in your Excel and determines the field type based on the type of data present in each column below the first row.

      Provide Data type

    13. Excel Cloud Link is a helper function to read a given range of data in your Excel. This will be returned through the Output Parameter. If you wish, you may edit the output parameter name on the right side.

      Edit Output Parameter

    14. Now you see the benefit of using Excel cloud link by printing output parameters. For this you will use a loop activity For Each.

      Add For Each

    15. As a loop variable, you can add the Output Parameter from Excel Cloud Link activity.

      Add Loop Variable

    16. Now you can print the customer name by adding a Log Message. If you are using the sample data provided in this tutorial you can print Order Number.

      Add Log Message

    17. Initially you add Customer.name.

      Add Customer Name

    18. At first it takes 0-th index. You use index variable, so for each loop iteration, it can print the corresponding value.

      Add Loop Index to the string

    19. Add Close Excel Instance as you added Open Excel Instance in the first step.

      Add Close Excel Instance

    20. Now you can test the output after saving the project.

      Save and Run project to validate result

    In the next step, you will take a more traditional approach to process the Excel data.

    What input information is required to use **ExcelCloudLink** activity

  • Step 3
    1. Lets start by adding a new automation to the project and add Open Excel Instance to the flow.

      Add Open Excel Instance

    2. You create a couple of string variables to create base folder and file name. Search for String and add it to the flow.

      Create String Variables FolderPath & FileName

    3. One of the variable will point to the base folder path and the second variable will point to the file name. Creating them in this form, helps to use them in subsequent activity types.

      Create String Variables FolderPath & FileName

    4. You call Get File Name activity to fetch the fileName. You can avoid this by creating a string variable for the file name.

      Get Excel File Name

    5. You add Open Workbook activity and point it to the full path (folder + file name) of the test Excel file. This will open the specific Excel file.

      Add Open Workbook

    6. Subsequently, you activate the workbook and the first worksheet. Input for the activate work book activity is the file name from the previous step and the input of the activate worksheet activity is the first sheet name. You can use variables or hard coded values for now. If you are trying a different Excel, use these values appropriately.

      Activate Worksheet

    7. Now that Excel sheet is open, you can apply filters, hide columns or get values from a range of cells or add a new sheet and copy a range of values to the newly created sheet.

      Activate Worksheet

    8. You can add Get Values (Cells) to fetch specific range cells and subsequently you can validate the step using output parameter through LogMessage. The input to Get Values (Cells) can be a single cell or a range of Excel cells. When you use Excel Cloud Link, reading Excel data is pretty quick and simple. The traditional approach requires a few steps.

      Add Get All Values

    9. Now Add Filter Range activity. In the Filter Range, you specify the data range, where the filter needs to be applied, along with the column on which the filter needs to apply with a filter condition.
      You add a new Excel sheet to your workbook and copy this filtered data onto that, just for demonstration purpose.

      Add Filter Range

    10. Subsequently you can add Hide column activity to hide a specific column in the result.

      Add Hide Column

    11. You copy the result of filter to a new sheet. For this you add a sheet through Add Worksheet and give it a name Result.

      Add a New Worksheet

    12. This will activate the new worksheet, but for us to copy from the filter results to this new sheet, you switch to the first sheet and copy its values.

      Activate original sheet

    13. Add Copy Range activity and provide the source range, destination worksheet and workbook and starting cell for copy.

      Add Copy Range

    14. Activate the new sheet now, in order to save it.

      Activate Result Sheet

    15. Now add Save As Workbook to save the new sheet as another file. You have the option to save workbook as an Excel file or CSV file. In order to save as a new CSV file, you will need a file path to store this CSV file. This will be of the format folderName + fileName without extension where it needs to be saved.

      Save As Workbook

    16. Finally, you need to include Close Excel Instance to conclude the step.

      Add Close Instance

    17. You can save the project and test it to validate the result.

      Run the project to test and validate

    18. At the end, when this tutorial will be tested, the below source data will be used:

      Source Excel Data

    19. After the automaton is executed, it has filtered the data (Town starting with a), hidden a column (Column F with title storey_range) and resultant Excel will look like this.

      Final Excel Data

    Excel SDK of SAP Build Process Automation offers a wide variety of activities you can consider for business processes.

    Review the below resources:

    What input information is required to use **FilterValues** activity

Back to top