How to output values from a VBA macro to UiPath

In my UiPath Facebook group, there is a ton of great information about RPA in general and UiPath in particular. Today one of the members posted a great question on passing log messages from an invoked VBA script (which you typically use when you automate Excel) back to UiPath. The task We have a simple […]

Table of Contents

In my UiPath Facebook group, there is a ton of great information about RPA in general and UiPath in particular. Today one of the members posted a great question on passing log messages from an invoked VBA script (which you typically use when you automate Excel) back to UiPath.

The task

We have a simple Excel macro-enabled workbook with a number between 1-100 in the A1 cell (here it's 50). The task is to run a VBA macro from UiPath and, in that VBA macro, check if the number is greater than 75. If yes, then the status should be "OK", else it's "Fail". The status should then be passed/outputted to UiPath as a log message.

Solution

Write the below-stated VBA code in a Notepad document and save it as a .txt file. I named it "TheVBACode.txt", but you can call it whatever you like.

It's a simple snippet that defines a function as a string and then checks if the value of A1 is greater than 75 and, based on that result, stores a value (OK or Fail) in the string variable 'Status'.

Finally we set the value of our TestLog function to the value of the 'Status' variable.

Private Function TestLog() as String

Dim Confidence As Integer, Status As String
Confidence = Range("A1").Value

If Confidence > 75 Then
    Status = "OK"
Else
    Status = "Fail"
End If

Range("B1").Value = Status

TestLog = Status

End Function

In UiPath, drag in an Excel Application Scope and in workbook path type in the path of your macro-enabled workbook (mine is in the UiPath project directory and is called "Data.xlsm".

Inside the "Do", we place an Invoke VBA. In "CodeFilePath", we type in the path of our saved VBA code (we created it above). I called mine "TheVBACode.txt," and it is in the UiPath project directory. In the "EntryMethodName", you type the name of your function (we called it TestLog). In "OutputValue" create a string variable with a given name (press ctrl+k and type in the name you want. I called it "strStatus".

Now this variable can be passed to a "Log Message".

Open the "Output" console in the lower-left corner, and you will find the log message with the value taken from the VBA macro.

Download the data and the VBA/UiPath solution here