SAP Intelligent Robotic Process Automation 2.0: Handle Dynamic Excel Files

In many use case we get the information in an Excel file, often with a known header but with an unknown number of rows.In this blog post you will learn how you can handle this dynamically filled Excel files in SAP Intelligent RPA 2.0 (version 2102).

The overall Automation will execute the following steps:

1. Open Excel Instance

2. Open Workbook

3. Get the used Excel cells

4. Get the values of a dynamic array

5. Close Excel

Open Excel Workbook

To use an Excel workbook we have first to start an Excel instance. Therefor we use the activity ‘Open Excel Instance’. This activity has no additional parameters.

With the activity ‘Open Workbook’ we can then open the workbook using the file path where the Excel file is located.

Of course we can also define the input parameter with a variable using the expression editor.

Get the Row or Column Index

The first alternative to get an information on the used rows and columns are the activities ‘Get Row From Data’ and ‘Get Column From Data’.

Both activities have the same structure with the optional input Parameters ‘referenceCell’ and verticalDirection / horizontalDirection. The ‘referenceCell’ is the starting point of the test for the used rows / columns. If the parameter is not filled the active cell in the Excel sheet is the starting point.

In the direction parameter we can define the direction for the test. This is the horizontal direction or the vertical direction to look for the end of the group of data. The output parameter delivers the ‘rowIndex’ or the ‘columnIndex’.

Get the Used Range Column Index or Used Range Row Index

The second alternative to get the information on the used rows and columns is to use the activities ‘Get UsedRange Row’ and/or ‘Get UsedRange Column’.

Both activities have the same structure and no input parameter. The output parameter delivers the ‘usedRangeRowIndex’ or the ‘usedRangeColumnIndex’.

Get the Values of a Dynamic Range

With the activity ‘Get Values (Cells) we get the data from the specified cells range in the active worksheet. The input parameters are the ‘rangeDefinition’ and the ‘defaultValue’.

To define the ‘rangeDefinition’ based on the used activities before we use the expression editor. In this case we want to get the data from the range starting with field A1 (1;1) until the end of the range combined out of the two indices.

We can test and save the expression. Using this parameters all values in a dynamic range will be delivered.

The default value in this activity is used to fill empty cells if necessary.

The output parameter provides the values of the range in the parameter ‘returnedValues’.

Close the Excel Instance

Last but not least we close Excel with the activity ‘Close Excel Instance’.

This activity has parameters.

Conclusion

Now we have learned how to handle Excel sheets with a dynamic number of rows and/or columns to solve a common problem in many uses cases for SAP Intelligent RPA.

For more information on SAP Intelligent RPA

• Exchange knowledge: SAP Community | Q&A | Blog
• Learn more: Webinars | Help Portal | openSAP |
• Explore: Product Information | Successful Use Cases
• Try SAP Intelligent RPA for Free: Trial Version | Pre-built Bots
• Follow us on: LinkedIn, Twitter and YouTube