-2

I am trying to retrieve cell addresses from an Excel sheet with UIPath invoke VBA activity.

I am new to VBA coding. 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'. 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.

This is the screen shot where I have enabled macros 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
        Cells(rngX.Row,rngX.Column).Delete
        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"
    Next
    CellArray = findcellFunction(Amount)
End Function

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

Community
  • 1
  • 1
NT.Tharu
  • 1
  • 1
  • 5
  • 2
    Post the vbscript code. Where is your macro saved? module? worksheet? – Mihai Adrian Jul 25 '18 at 07:08
  • 2
    Also `On Error Resume Next` as first line without a proper error handling is a very bad idea. This hides all error messages in the whole function, but the errors still occur, you just cannot see them. This makes you blind and impossible to see errors and therefore you cannot debug and fix your code. Remove that line, see if you get errors and fix them. Hiding errors is never an option. – Pᴇʜ Jul 25 '18 at 07:15
  • @MihaiAdrian macro is saved in a module. – NT.Tharu Jul 25 '18 at 07:23
  • @Pᴇʜ I am afraid, As you said I removed that line and run again. But it gives same error. Also I will keep that in mind. – NT.Tharu Jul 25 '18 at 07:26
  • Why would you use `On Error Resume Next` in VBA? Surely you'd use `On Error Goto – user692942 Jul 25 '18 at 08:15
  • @Pᴇʜ I execute the vbs file from uipath's invoke vba activity. – NT.Tharu Jul 25 '18 at 08:18
  • @NT.Tharu that isn't VBScript (VBS), it has a `.vb` extension. You're invoking the VBA module direct from the UIPath GUI. – user692942 Jul 25 '18 at 08:21
  • @Lankymart As i had learnt from internet it is to ignore errors. I am new to vba and vb script . Now i have learnt it is a bad idea to include it. – NT.Tharu Jul 25 '18 at 08:22

1 Answers1

0

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.

NT.Tharu
  • 1
  • 1
  • 5