Is VBA good for RPA

Excelblog.ch

Recurring activities can be automated with Robotic Process Automation (RPA). This aspect is closely related to macros - and RPA is definitely seen as the next level of macros (especially due to the fact that every application or data source can be connected, often by means of interaction via the user interface).

The common RPA tools have built-in Excel activities. Some possibilities with the "Kofax" solution have already been discussed in this article. The figure below shows some standard activities in the "UiPath" software with which the example in this article is worked out:

These options already allow an enormous number of activities - you can therefore integrate repetitive Excel steps that a user carries out directly from the RPA software into the entire workflow. However, if you are already familiar with VBA, you are more likely to rely on this horse, because you expect more flexibility and other advantages from (your own) VBA code. The decision depends on the application and should be evaluated in each case. My recommendation at all times is that you rely on the strengths of connected tools and use RPA as a link.

The data extraction

We are working on the following example in this article: We access an internal "sales portal" via the web. On it, among other things, certain information and activities of our field staff can be viewed in table form. We would like to present the information in the table "Prospecting Activities per Sales Manager" better in a separate Excel report. The mentioned table looks like this:

You are also welcome to experiment with our demo data, the test environment can be found under the following links:

https://www.routinuum.ch/demo-sites/MAB/SalesPortal.html

A login has been set up, whereby you can simply click on the "Login" button to get to the page with the sales information (alternatively, navigate directly to this link: https://www.routinuum.ch/demo-sites/MAB/ SalesData.html).

In a manual process, this information would be entered or copied into an Excel table. With an automation in UiPath, we log into the sales portal directly and extract the data accordingly. The sequence in UiPath can be designed as follows:

With "Write Range" activities in UiPath, this extracted table can then be written to any location in Excel (see below).

Combination with VBA to a holistic process

After the data extraction, repetitive work steps would again be necessary in order to prepare the report accordingly. Here in particular, macros (VBA) have often been used to date. For this example I have also prepared a short VBA script that aggregates and displays the raw data and the "actual values" in a pivot table. The end result looks like this:

But how can you combine RPA (UiPath) and VBA with each other? The careful reader may have noticed two activities in the very first illustration: "Execute Macro" or "Invoke VBA". We will work here with the former activity. After the "Write Range" activity to write the extracted data to the Excel spreadsheet, we add the "Execute Macro" step. We put the name of the macro in quotation marks - in my case "PrepareData". These are the activities within the "Excel Activities / Macro" sequence in UiPath:

It is already! The robot process independently opens the browser, logs into the sales portal, extracts the data from the "Prospecting Activities" table, transfers them to the Excel file and executes the separately created macro. The complete process can be structured as follows:

The first sequence "Get Credentials (Login)" asks for possible login details from the Windows Credentials Manager (login information management) and safely draws this information into the robot process. We have published a detailed article on this as a post on the Routinuum GmbH website and on LinkedIn (English).

Further considerations for the example process

A big advantage of RPA is that you can tap into any conceivable data source. Assume that a national company is not yet connected to the "Sales Portal" presented. We receive the information by email at the appropriate period, for example with an accompanying Excel file. The illustrated process can be extended as required by email activities (e.g. Outlook) so that the file attachment is saved. With the "Append Range" activity, this additional information can easily be appended to the existing table from the Sales Portal data extraction. Through dynamic areas in the created macro - for example with the line of code LastRow = Range ("A1"). End (xlDown) .Row to determine the last line - the macro also includes further entries in the corresponding pivot table.