Please help me to solve this problem. uipath invokeVBA activity gets any variant we return from a function as a “COM Component/object” . So I am returning a collection from the macro. and invokeVBA gets it as a COM object(lets refer it as OBJECT) . So i need to convert/read it into a collection again. and i am unable to do it. i am trying this for a week now. Please help.
color code excel example.xlsx 1 (7.9 KB)
find cell adresses.xaml (9.4 KB)
These are the excel i am trying get cell values collection and the uipath module that i have created.
Below please find the macro(vba).
Function findcellFunction(ByAmount As Integer)As Collection 'On Error Resume Next Dim rngX As Range Dim WS As Worksheet Dim datax As Range Dim cellAddress As Variant Dim index As Integer Dim iTotal As Integer Dim CellArray iTotal = 0 Set CellArray = New Collection For index=1 To Amount Set rngX = Worksheets("Sheet1").Range("A1:EZ50").Find("Color Name", lookat:=xlPart) If Not rngX Is Nothing Then MsgBox "Found at " & rngX.Address CellArray.Add rngX.Address End If Cells(rngX.Row,rngX.Column).Delete iTotal =iTotal + index Next index For Each cellAddress In CellArray MsgBox "list populated " & cellAddress Range(cellAddress).Value = "Color Name" Next Set findcellFunction= CellArray End Function