SAP Intelligent RPA: Reading Data from Excel using VBA Excel reference properties

[vutkurivenkatasaicharan]

Introduction

This is to ease the reading of excel files which could have unknown number of rows to be read.

In this post I will show you how to read an Excel file with unknown number of rows using VBA Excel reference properties and custom scripts

What will you learn in this blog:

• Basics of excel reading


• Reading excel file using custom scripts


• VBA Excel reference properties that can make Excel reading simple


• Reading unknown number of rows without loops in workflow

Brief on Excel VBA reference that would be used to get all the Values without any Loops:

• Excel VBA Object model has  Application object which refers to the entire Microsoft Excel application
  
  
  
  • Application object has property ActiveSheet that returns the active Worksheet in the active excel
  
  
  
  


• WorkSheet object , in Excel VBA objects, which represents the Worksheet of the excel
  
  
  
  • WorkSheet object has property UsedRange that returns the Range on the Worksheet that has been used or filled
  
  
  
  


• Range object represents one cell or block of cells
  
  
  
  • Address property of Range, provides the representation of range as string
  
  
  
  

Steps to follow:

1. Create a project;


2. Create a new Workflow;


3. Import Excel Library Scripts;


4. Designing Workflow with Custom Scripts


5. Display range address and random cell value

Prerequisites:

• Desktop Studio


• Microsoft office

Instructions:

 

1. Create a project

 

Open Desktop Studio and create a new project from File menu. Give it a name

2. Create a new workflow

 

Go to Workflow perspective , right click on Global and add new workflow give it a name.

       

 

3.Import Excel Library Scripts

 

Whenever you create a project, its framework includes most of the Libraries that would be useful for creating the workflows. Though you can drag and drop or use the Excel Lib function in your workflows, until you include the Excel Library workflow would run into errors during compilation and execution.

To include excel library,

go to edit project in the file menu, navigate to Libraries and select Excel integration



Or go to Scripts, right click on Global, click Include Library Script and select Excel Integration

 

 

4. Designing Workflow with Custom Scripts

 

• Add Activity of Initialize Excel which initializes the Excel library and mode parameters
  
  
  
  • Mark the Initialization as start node with a right click and selecting Set as Start Node
  
  
  
  

• Next activity is to open the excel file, we add open existing Excel file ,from the activity list functions of Excel Lib. Filename must be a string and if path has been provided the separator would be \\ between the folders.

• In case the workbook opened has multiple worksheets, we need to activate the sheet to be read, use Activate worksheet activity. If workbook has only one worksheet, it is optional activity. As the workbook used has multiple sheets, the following activating function has been added.

• Add Custom activity, to read the data from the sheet activated

• Once we generate the build for workflow created, we would be able to edit the custom activity to include the custom script. Click on the build to generate the script for the workflow.

• Go to Scripts -> workflow script -> Custom step to add the code to extract data using VBA properties

	var contents = [];

	contents = getContents();

        

• Go to Scripts and right click to add custom library script

• 
  
  
  
  • Define the function getContents that has been used in Custom Step of the Workflow
    
    
    
    • ctx.excel.application.getObject is available in Excel Library which fetches the Excel object. For more information refer to the documentation of the library.
    
    
    • Application.ActiveSheet is Excel VBA property which fetches the active Worksheet
    
    
    • UsedRange is the property of worksheet that returns the Range Object
    
    
    • Address property of the Range provides the address of the range object as string . Ex: '$A$1:$B$3'
    
    
    • ctx.excel.sheet.getValuesFromRangeDefinition takes the range definition as string. For more information refer to the documentation of the library
    
    
    
    
  
  
  
  

var oRangeValues = [];

var coreApp =  ctx.excel.application.getObject();

var activesheet = coreApp.Application.ActiveSheet;

ctx.log(activesheet.Name,e.logIconType.Info); // For validation

var rangeObject = activesheet.UsedRange;

ctx.log(rangeObject.Address,e.logIconType.Info); // for Validation

var rangeDefinition = rangeObject.Address.replace('$','');

oRangeValues = ctx.excel.sheet.getValuesFromRangeDefinition(rangeDefinition,'');



ctx.log('rangeValues at 1,1',e.logIconType.Info); // For Validation

ctx.log(oRangeValues[1][1],e.logIconType.Info); // For Validation

 

 

5. Display Range address and Random Cell Value

 

• ctx.log has been used to write the property data to the logs for validation


• Log will be written with the Active sheet Name , Range Address and cell value at (1,1) position

Following file has been used,:

• File has two sheets with name 'header' and 'item'.


• Active sheet would be 'header'


• Range to be read would be "A1:H5"


• Cell Value at (1,1) position would be 'A'

Following is the context log

 

Note:

As Excel VBA objects and properties used in Library of SAP IRPA, there would be warnings but the properties would be available during runtime.

Conclusion

Using Excel VBA Object properties in SAP Intelligent RPA would give the bot less time to read the whole data without using explicit loops. You can reuse the custom library to read the Excel for any dimensions