I am trying to retrieve some cell addresses from excel sheet with UIPath invoke VBA activity. I am new to VBA coding, here I have written a function to retrieve cell address array and I have written a sub procedure to call that function. I am calling the sub in UIPath activity named' invoke VBA. So when I run this it says

Invoke VBA : Cannot run the macro 'Main'. The macro may not be available in this workbook or all macros may be disabled.

So Please help me to get over with this?


This is the screen shot where I have enabled macro in Excel: screen shot

Sub Main(Amount As Integer)
    Call findcellFunction(Amount)
End Sub

Function findcellFunction(Amount 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
    'Iterate until all cell values are found
    For index=1 To Amount
        Set rngX = Worksheets("rptBOMColorPrint").Range("A1:EZ50").Find("Colour Name", lookat:=xlPart)
        If Not rngX Is Nothing Then
            MsgBox "Found at " & rngX.Address
            CellArray.Add rngX.Address
        End If
        iTotel =iTotal + index
    Next index
    'shows list that has been populated with cell addresses
    For Each cellAddress In CellArray
        MsgBox "list populated " & cellAddress
        Range(cellAddress).Value = "Colour Name"
    CellArray = findcellFunction(Amount)
End Function

This is how I call the VBA from the UIPath activity. How I call the VBA macro from UIPath

1 Answers

NT.Tharu On Best Solutions

When you include a .xlsx excel file instead of .xls file it works. It is because Uipath invoke VBA activity does not support .xls files as it is older version.