Working with 2D Object Array

Agentic Automation
,
1

Hey guys, I am working with Invoke VBA Activity to get a 2D output Array from a Script.

Thing is, I can successfull obtain said variable (highlighted in screenshot below):

image
image1021×249 8.59 KB

A portion of it is pasted below:
object[75, 3] { { "0", "AA1H1Q01A3CB1/XCBR1.Pos.ctlVal", "DPS" }, { "0", "AA1H1Q01A3Dc1/XSWI1.Pos.ctlVal", "DPS" }, { "0", "AA1H1Q01A3CB1/CSWI1.Pos.ctlVal", "DPC" }, { "0", "AA1H1Q01A3Dc1/CSWI1.Pos.ctlVal", "DPC" }, { "0", "AA1H1Q01A3Application/USER2.SPC.ctlVal", "SPC" }, { "0", "AA1H1Q01A3Application/USER1.SPS.stVal", "SPS" }, { "0", "AA1H1Q01A3Application/USER1.SPS1.stVal", "SPS" },....

Now I have to count how many rows there are, and paste said Object variable in an Excel Sheet (through DataTable and AddRow perhaps?). Main error I get is:

How can I solve this? My workflow is the following:
Main.xaml (25,3 KB)

VBScript so you can compile it is:

' SE EXTRAEN LOS DATASETS EN BRUTO, SIN NINGÚN ORDEN
Function DatasetsExtraction(ByVal xmlRuta As String) As Variant()
	Dim xmlCID1, IEDNodes, dataSetNodes, dataSet, dpNode, spsNodes, sps, fcdaNodes, fcda As Object
	Dim dpString As String
	Dim numSenales As Long, i As Long
	Dim TKName, DSName, LDName, LNName, DOName, DAName, tipoLS, cadena As String
	Dim descCell, tipoCell, equCell As String
	Dim startDPC As Integer, numEquipos As Integer
	Dim cadenaArray() As String, tipoArray() As String, descArray() As String
	Dim datosArray() As Variant
    
    ' Crear documento XML
    Set xmlCID1 = CreateObject("MSXML2.DOMDocument")
    xmlCID1.async = False
    xmlCID1.Load xmlRuta

    If xmlCID1.ParseError.ErrorCode <> 0 Then
        MsgBox "Error al cargar XML: " & xmlCID1.ParseError.Reason
        Exit Function
    End If

    ' EXTRACCIÓN DE LA TABLA EN BRUTO, SEGÚN ORDEN DEL CID
    ' Extraigo los nodos llamados DataSet
    Set dataSetNodes = xmlCID1.getElementsByTagName("DataSet")  ' Busco elementos con el tag DataSet
    ' Identifico el nodo llamado IED, para extraer el Technical Key
    Set IEDNodes = xmlCID1.getElementsByTagName("IED")
    TKName = IEDNodes(0).getAttribute("name")
    
    ' RECORRIDO DE CADA DATASET PARA EXTRAER SEÑALES
    numSenales = 0
    For Each dataSet In dataSetNodes    ' Se recorren todos los Datasets encontrados
        Set fcdaNodes = dataSet.getElementsByTagName("FCDA")    ' Se ubican los Functional Constraint Data Attribute a partir de su tag
        For Each fcda In fcdaNodes      ' Se recorre cada FCDA
            DSName = dataSet.getAttribute("name")
            LDName = fcda.getAttribute("ldInst")
            LNName = fcda.getAttribute("lnClass") & fcda.getAttribute("lnInst")
            DOName = fcda.getAttribute("doName")
            typeXML = fcda.getAttribute("fc")
                
            If InStr(1, LNName, "CSWI", vbTextCompare) Or InStr(1, DOName, "SPC", vbTextCompare) Then
                DAName = ".ctlVal"
                If InStr(1, LNName, "CSWI", vbTextCompare) Then
                    tipoLS = "DPC"
                Else
                    tipoLS = "SPC"
                End If
            ElseIf InStr(1, LNName, "XCBR", vbTextCompare) Or InStr(1, LNName, "XSWI", vbTextCompare) Then
                DAName = ".ctlVal"
                tipoLS = "DPS"
            ElseIf typeXML = "ST" Then
                DAName = ".stVal"
                tipoLS = "SPS"
            ElseIf typeXML = "MX" Then
                DAName = ".cVal.mag.f"
                tipoLS = "MX"
            ElseIf InStr(1, LNName, "Op", vbTextCompare) Then
                DAName = ".general"
                tipoLS = "ACT"
            ElseIf InStr(1, LNName, "Str", vbTextCompare) Then
                DAName = ".general"
                tipoLS = "ACD"
            End If
                
            cadena = TKName & LDName & "/" & LNName & "." & DOName & DAName
            ReDim Preserve cadenaArray(0 To numSenales)
            cadenaArray(numSenales) = cadena
            ReDim Preserve tipoArray(0 To numSenales)
            tipoArray(numSenales) = tipoLS
            ReDim Preserve descArray(0 To numSenales)
            descArray(numSenales) = "0"
	
            numSenales = numSenales + 1
        Next fcda
    Next dataSet
	
	' PASO LOS ARRAYS A LA TABLA MAESTRA
	ReDim datosArray(numSenales, 2)
    For i = 0 To numSenales - 1
        datosArray(i, 0) = descArray(i)
        datosArray(i, 1) = cadenaArray(i)
        datosArray(i, 2) = tipoArray(i)
    Next i

    DatasetsExtraction = datosArray
End Function
2

@romulo.prieto.l

The problem is because you are assigning an object to integer..either change the type of left side variable to object or use cint(datosArray(0,0) on the right

Its a type casting issue

Cheers

3

Hi Anil, despite changing the numSenales variable type to Object and Assigning:

numSenales = datosArray(0,0)

Or performing the cast you mentioned, I still get Error: Strict On disallows late binding. Any ideas?

image
image1161×536 29.3 KB

4

@romulo.prieto.l

The problem is your array ..it is not declared as array but as an object..instead declare as array of array of object …that will set it

Cheers

5

Ok changed it, however, now I get:

image
image1464×324 30.1 KB

Error is in Invoke VBA, not the Assign activity.

If it is a must to have the variable as an Object, how should the array be returned from the VBA Script? Right now I am only using Variant:

Function DatasetsExtraction(ByVal xmlRuta As String) As Variant()
.
.
.
Dim datosArray() As Variant
6

@romulo.prieto.l

Okay..then try to assign another variable for outpu argument which comes as object then case it to array of array of ovject and check if that resolves

The problem is .net 5+ is strict on types

Mostly this also might not work with same casting issue..if it doesnot then get as atring and then convert as required array object by splitting the delimiters

Cheers

7

Like this?:

image
image1126×742 45.5 KB

Now error is:
Assign: Unable to cast object of type ‘System.Object[,]’ to type ‘System.Object’.

To be sure, you are referring to changing the script output to an array, using delimiters like: “A#B#C, D#E#F…”?
Tried it that way, it works but due to the nature of my project any character may be used inside the string elements in the array, I wanted to avoid that approach.

8

@romulo.prieto.l

Thats the problem of casting as mentioned

You can use delimitters like /-/ which generally wont be used..any combination of delimiter which can be as wierd as possible

Cheers

9

Hey Anil,
Used the delimeter you suggested “/-/” and now it works wonderfully:

A = datosArray(i).Split(New String() {"/-/"} , StringSplitOptions.None)(0)
B = datosArray(i).Split(New String() {"/-/"} , StringSplitOptions.None)(1)
.
.
.

Just for the record, using a 2D Array leads to errors because of the .net+5 compiler right? This is the only (or most efficient) way around it then.

1 Like
10

@romulo.prieto.l

Glad you were able to resolve the issue

yes that correct. this would be the way

cheers

1 Like