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

2. Provide an appropriate name to your project such as Excel Sample and relevant automation such as excelLink.

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.

   

   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.

10. Under Workbook Path, provide the path of the workbook that will be used during the automation execution.

    

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.

    

12. Provide a data type name such as Sales Orders and choose Create.

    

    Excel Cloud Link will identify field names using the first row of each column in your Excel and will determine the field type based on the type of data present in each column below the first row.

    The activity is also a helper function to read a given range of data in your Excel. This will be returned through the Output Parameter.

13. If you wish, you may edit the output parameter name on the right side to salesOrders.

14. Save and close the activity screen.

    

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

    

16. As a loop variable, add the Output Parameter salesOrders from the Excel Cloud Link activity.

    

    If you are using the sample data provided in this tutorial you can print Order Number.

17. Drag and drop a Log Message activity into the workflow.

    

18. Select the Log Message activity and change the Step name to Log Order Number.

19. Under the message input parameter, choose the Open the expression editor icon.

20. In the Edit Expression window, type: "Order Number is " +

21. Then select currentMember>orderNumber from the list of Variables.

22. Choose Save Expression.

    

23. Add Close Excel Instance after the For Each loop as you added Open Excel Instance in the first step.

24. Choose Save to save your project.

    

25. Now you can test the output.

    

• SAP Help Documentation [IRPA SDK V2] (https://help.sap.com/doc/b8b5c9bbac3846a0a57aedab3f412880/Cloud/en-US/modules/helper.html) 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. Start by adding a new automation to the Excel Sample project. Name the new automation Process Excel Data and add Open Excel Instance to the flow.

   

2. You will create two string variables. Search for String and drag and drop it into the flow twice.

   

3. Select the first Create String Variable and change the Step name to Create Workbook Path Variable.

4. Under Input Parameters as value, enter the full path to the workbook.

5. Under Output Parameters, change the name to WorkbookPath.

   

6. Select the second Create String Variable and change the Step name to Create Excel File Name Variable.

7. Under Input Parameters as value, enter the name of the excel file.

8. Under Output Parameters, change the name to FileName.

   

   Creating the variable in this form, helps to use them in subsequent activity types.

9. Now add an Open Workbook activity.

   

10. Under workbookPath input parameter, select the full path 2 WorkbookPath of the Excel file. This will open the specific Excel file.

    

11. To activate the workbook and the first worksheet, do the following:

    • In the Automation Details panel, search for the activities Activate Workbook and Activate Worksheet.
    • Drag and drop the activities into the canvas.

    

    • Under workbookName input parameter, select 3 FileName.

    

    • Under worksheetName input parameter, enter Sheet1 and select the expression in quotes.

    • Save your work.

    

    You can use variables or hard coded values for now. If you are trying a different Excel, use these values appropriately.

    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.

12. Drag and drop a Filter Range activity into the workflow.

    

13. Select the Filter Range activity.

14. Under Input Parameters, specify:

    • the data range, where the filter needs to be applied such as A1:E11.

    • the column on which the filter needs to be applied such as column B.

    • a filter condition such as <1000000

    

    You add a new Excel sheet to your workbook and copy this filtered data onto that, just for demonstration purpose.

15. Subsequently add a Hide columns activity to hide a specific column in the result.

    

16. Select the activity and under rangeReference input parameter, enter C1.

    

    You will then copy the result of filter to a new sheet. For this you add a sheet through Add Worksheet activity.

17. Drag and drop a Add Worksheet activity into the workflow.

    

18. Under worksheetName input parameter, enter the name Result.

    

19. Add Activate Worksheet activity to activate the new worksheet.

20. Select the activity and under worksheetName input parameter, enter the name of the first sheet of your original excel file such as Sheet1.

    

21. Add a Copy Range activity, select the activity and provide the following input parameters:

    • the rangeDefinition as A1:A11.

    • the destinationRange as A1.

    • the destinationWorksheet as Result.

    • the destinationWorkbook as 3 FileName.

    

22. Now add Save As Workbook activity to save the new sheet as another file.

    You have the option to save workbook as an Excel file or a 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.

    

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

    

24. Save the project and test it to validate the result.

    

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

    

26. After the automaton is executed, it has filtered the data, hidden a column (Column C) and resultant Excel will look like this.

    

    A new worksheet named Result will be created:

    

    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