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

Anders Jensen

RPA DEVELOPER, YOUTUBER & UIPATH MOST VALUED PROFESSIONAL 2021

Anders Jensen is the RPA Lead at Lessor A/S (Part of Paychex Inc) and an advanced certified UiPath RPA Instructor.

Using his extensive experience in automating interfaces such as Windows, SAP, and browsers, Anders develops enterprise RPA solutions automating work for customers and colleagues one task at a time.

In the evenings and weekends, Anders is passionate about teaching others RPA by making instructional videos on his YouTube channel.

View all posts

2 comments

Your email address will not be published. Required fields are marked *

  • Helpful Tip:
    Change your Excel Trust center settings
    “File–>options–>trust center–>trust center Settings–>Enable Trust access to the VBA project object model”

    • Hey Algorithm. Thanks a lot. Can you elaborate, to the viewers of this and me, why it’s helpful? 🙂 Usually, I just have a trusted folder. Kind regards, Anders

Anders Jensen

RPA DEVELOPER, YOUTUBER & UIPATH MOST VALUED PROFESSIONAL 2021

Anders Jensen is the RPA Lead at Lessor A/S (Part of Paychex Inc) and an advanced certified UiPath RPA Instructor.

Using his extensive experience in automating interfaces such as Windows, SAP, and browsers, Anders develops enterprise RPA solutions automating work for customers and colleagues one task at a time.

In the evenings and weekends, Anders is passionate about teaching others RPA by making instructional videos on his YouTube channel.