VBA to Get cell not working in Uipath

Help Activities
, ,
1

Hi Team Im using this below VBA code to filter EMP ID column & EMP NAME using VBA . The Macro is returning right value when I’m running that macro in excel but When I invoke this UiPath invoke activity im getting the output as Null.

Please let me know where I am missing the link to get output in my empname variable

Parameters I have passed -
Input - {empid,empid column,emp name column,empName}
Output - empName - being created as object

VBA Code

Sub FilterAndExtractData(empID As String, EmpIDcomlumn As String, empNameColumn As String, ByRef empName As String)
Dim ws As Worksheet
Dim filterRange As Range
Dim foundCell As Range

' Set the worksheet (change "YourSheetName" to your actual sheet name)
Set ws = ThisWorkbook.Sheets("Sheet2")

' Assuming your data starts from column A, adjust the column as needed
Set filterRange = ws.Range("A1").CurrentRegion

' Apply the filter based on 'EmpID' column
filterRange.AutoFilter Field:=ws.Rows(1).Find(EmpIDcomlumn).Column, Criteria1:=empID

' Find the first visible (filtered) cell in the 'EmpName' column
Set foundCell = ws.Rows(1).Find(empNameColumn)

' Check if a matching cell is found
If Not foundCell Is Nothing Then
    ' Get the value from the corresponding row
    empName = ws.Cells(filterRange.Offset(1, foundCell.Column - 1).SpecialCells(xlCellTypeVisible).Row, foundCell.Column).Value
Else
    ' If 'EmpName' column not found, set empName to an error value or handle accordingly
    empName = "Column not found"
End If

' Turn off the filter
ws.AutoFilterMode = False

End Sub

2

Hi @Ana_Patricia

Try this VBA code:

' Set the worksheet (change "YourSheetName" to your actual sheet name)
Set ws = ThisWorkbook.Sheets("Sheet2")

' Assuming your data starts from column A, adjust the column as needed
Set filterRange = ws.Range("A1").CurrentRegion

' Find the column number for 'EmpID'
Dim empIDColumn As Long
empIDColumn = ws.Rows(1).Find(EmpIDcomlumn).Column

' Apply the filter based on 'EmpID' column
filterRange.AutoFilter Field:=empIDColumn, Criteria1:=empID

' Find the first visible (filtered) cell in the 'EmpName' column
Set foundCell = ws.Rows(1).Find(empNameColumn)

' Check if a matching cell is found
If Not foundCell Is Nothing Then
    ' Get the value from the corresponding row
    Dim visibleRow As Range
    On Error Resume Next
    Set visibleRow = filterRange.Offset(1, foundCell.Column - 1).Resize(filterRange.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Not visibleRow Is Nothing Then
        empName = ws.Cells(visibleRow.Row, foundCell.Column).Value
    Else
        ' Handle the case when no visible cells are found
        empName = "No matching data"
    End If
Else
    ' If 'EmpName' column not found, set empName to an error value or handle accordingly
    empName = "Column not found"
End If

' Turn off the filter
ws.AutoFilterMode = False

Hope it helps

3

Thanks for Responding, its the same … its giving the emp name as null… Its not returning the value.

The one which I posted was working fine when I run in excel directly through macro but its not being returned when using with UiPath?

Is there any issue with passing outargument?

4

@Ana_Patricia

If possible can you share the Excel file so that we can find out the exact problem.

Regards

5

@Ana_Patricia

Please create a function instead of sub…function can return a value not sub

And try like below…

Function FilterAndExtractData() As String

All your code here

Instead of empName = ws.cells ….

FilterAndExtractData = ws.cells….

Also 

Instead of empName = "Column Not Found"

FilterAndExtractData = "Column Not Found"

End Function

Hope this helps

Now you would get a return value in UiPath

Cheers