You are currently viewing How to output values from a VBA macro to UiPath

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 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 & TWO-TIME UIPATH MOST VALUED PROFESSIONAL (2021 & 2022) Anders has been running andersjensenorg fulltime since December 2021. The company specializes in teaching RPA via YouTube and tailormade learning paths. Anders is an experienced RPA-developer and -teacher with experience in both the public and private sectors. Anders’ YouTube channel has trained more than 100.000 Citizen Developers in just one video. Alongside his YouTube channel, Anders has built a unique global RPA-community with developers on all levels. When using Anders as your partner for RPA-trainings, you get direct access to this unique opportunity for problem-solving, opportunities, and networking.

This Post Has 2 Comments

  1. Algorithm Eswar

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

    1. Anders Jensen

      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

Leave a Reply