Invoke VBA

invoke

#1

I am using Invoke VBA to call a VBA Function in an Excel File. I have it setup as follows:

  • Excel File (.xls) file with no Macros
  • Excel is configured to allow macros
  • A separate file with the Macro Code

When I use the Invoke VBA activity it comes up with the following error:

Cannot run the macro ‘SelectSheetContents’. The macro may not be available in this workbook or all macros may be disabled.

To me that suggests the VBA Function needs to be in the Excel File, but I thought that is what this activity was able to get around.

Please Advise what I am doing wrong.


#2

I’m having similar issue. So if you get an answer off this string can you let me know. Thanks!


#3

Did you try the same macro on other spreadsheets? Can you share the macro file?


#4

It was working when I was testing it outside the process but now I’ve put it in the process and getting errors. Here is what I have. . .
image

Now I’m getting this error so I’ll work on it now.

Here is the Macro:
Attribute VB_Name = “Module1”
Sub OMNI()
Attribute OMNI.VB_ProcData.VB_Invoke_Func = " \n14"

’ OMNI Macro


Sheets(“Sheet1”).Select
Sheets(“sheet1”).Name = “Downloaded File”
Sheets.Add After:=ActiveSheet
Sheets(“Sheet1”).Name = “Formatted File”
Sheets(“Downloaded File”).Select
Cells.Select
Selection.Copy
Sheets(“Formatted File”).Select
Cells.Select
ActiveSheet.Paste
Columns(“A:C”).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns(“D:F”).Select
Selection.Delete Shift:=xlToLeft
Columns(“F:F”).Select
Selection.Delete Shift:=xlToLeft
Columns(“D:D”).Select
Selection.NumberFormat = _
([$$-en-US]* #,##0.00);([$$-en-US]* (#,##0.00);([$$-en-US]* “”-”"??);(@)"
Columns(“G:G”).Select
Selection.NumberFormat = _
"
([$$-en-US]* #,##0.00_);([$$-en-US]* (#,##0.00);([$$-en-US]* “”-""??);(@_)"
Cells.Select
Dim row, lastrow As Long
Dim Criteria1, Criteria2 As String

lastrow = ActiveSheet.UsedRange.Rows.Count

For row = lastrow To 2 Step -1

Criteria1 = Cells(row, 7)
Criteria2 = Cells(row - 1, 7)


If Criteria1 <> Criteria2 Then Cells(row, 1).EntireRow.Insert
 
Next row
    Cells.Select
Selection.Subtotal GroupBy:=6, Function:=xlCount, TotalList:=Array(6), _
    Replace:=True, PageBreaks:=True, SummaryBelowData:=True
ActiveSheet.ResetAllPageBreaks
End Sub

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1
Set ws = Sheets("Sheet1")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).row
title = "A1:C1"
titlerow = ws.Range(title).Cells(1).row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

#5

I think I have resolved my issue, I think I had another spreadsheet open with the Visual Basic Editor open and this was causing the COM Exception.

If I ensure that Excel is closed and run the Invoke VBA it appears to work as expected.


#6

I resolved mine also. Had to take it out of the sequence and make it on its own and I’m all good.


#7

Just wanted to add another piece of information to this thread as it fits nicely.

If you are getting the error programmatic access to visual basic is not trusted then see this link for settings in your Excel file.

R2D2


#8

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 (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

#9

I’m out of town for 12 days but will look at it when I get back.


#10

@ovi @badita

Is this an active forum? Could you please provide an answer for this


#11

I’ve been out of town I will provide an answer Monday when I return.


#12

hi , could you please help us @vvaidya


#13

I will when I get in the office around 9AM CST


#14

Naduni-Are you still needing assistance on this? I just got back in town. I don’t know if I can help or not but will look into it.


#15

So I’ve looked at what i did on my process:

I don’t know what your macro is but i had two different situations so i had two that i invoked on a condition.


#16

@vvaidya franckly
what we are trying to do is returning a Collection/Array/List from VBA like this.

Function findcellFunction()As Collection
	Dim CellArray
	Set CellArray = New Collection
	CellArray.Add "xyz"
	CellArray.Add "abc"
	Set findcellFunction = CellArray
End Function

and converting that via uipath into a collection/array/List again like this.

collectionConvert = CType(outputCOMObject , Collection)

So when VBA is returning the collection/Array/List it retrieves it as a System._COMObject .
So when we try to convert into collection again via CType method…it gives following error.
@lasithdilshan20

So we need to find a way to convert the COMObject into a collection/Array/List . Please help.


#17

Can you send me your macro so I can see the whole process? That is the one missing piece for me.
Thanks!


#18

color code excel example.xlsx (7.9 KB)
just use this excel file.

then add this vba file in invokeVBA.

return Collection.vb (188 Bytes)

this is the uipath module.

collection return via vba.xaml (8.6 KB)


#19

Actually, we just need to know how to return collection via vba and get the out put in uipath just like we do to String, Integer kind of data types.

Thanks :slight_smile:


#20

That is not what I did and I’ve never done it but I’m happy to look at this and see if I can get it to work for you.