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

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:

You can also use an Excel sample of your choice.

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.
   

   

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

   

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.
   

   

8. Within the edit activity screen, you can either use the file picker or drag and drop your test 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.
   

   

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.
    

    

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).
    

    

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.
    

    

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.
    

    

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.
    

    

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

    

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.
    

    

17. Initially you 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.
    

    

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

    

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

    

• SAP Help Documentation IRPA SDK V2 on Excel Cloud Link
• Excel Cloud Link How to use the Excel Cloud Link activity helper
• Community Quick automation with SAP Intelligent RPA – Excel SDK | SAP Blogs on Excel SDK

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

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

   

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

   

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.
   

   

4. You call Get File Name activity to fetch the fileName. You can avoid this by creating a string variable for the 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.
   

   

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.
   

   

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.
   

   

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.
   

   

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.
   

   

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

    

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.
    

    

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.
    

    

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

    

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

    

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.
    

    

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

    

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

    

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

    

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.
    

    

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

Review the below resources:

• Microsoft Excel Best Practices from the SAP Build Process Automation documentation
• Excel Email Best Practices Automation from the SAP Build Process Automation Store