Excel Array as Variant Error

[Eugene_Dakin]

Hello Everyone,

I have a problem where I am unable to convert an array into an excel TextToColumns method. Here is the code that works in VBA:

Range("A1:A4").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

I am getting an error when converting the Excel array to a Variant. Here is my converted Xojo Code.

Sub Action() Handles Action
Dim excel As New ExcelApplication
excel.visible = True

excel.workbooks.add
excel.Range(“A1”).value = “Brady, Tom”
excel.Range(“A2”).value = “Manning, Peyton”
excel.Range(“A3”).value = “Peterson, Adrian”
excel.Range(“A4”).value = “Lewis, Ray”

Var Z1 As Variant = 1
Var Z2 As Variant = 2
Var Z3 As Variant = 3
Var V1() As Variant = Array(Z1,Z1)
Var V2() As Variant = Array(Z2,Z1)
Var V3() As Variant = Array(Z3,Z1)
Var A() As Variant = Array(V1,V2,V3)

excel.Range(“A1:A4”).TextToColumns(excel.Range(“A1”),Office.xlDelimited, Office.xlDoubleQuote, True, False, False, False, True, False, A,True)
End Sub

In the code Var A() As Variant = Array(V1,V2,V3) I get the following error:

This array method works only for one-dimensional arrays
Var A() As Variant = Array(V1,V2,V3)

I know its a longshot, and does anyone know how to convert a multidimensional array to a variant for Excel on Windows?

[MarkusR]

seems this argument is the columns datatype XlColumnDataType
maybe cou can set it in different way.

[Greg_O_Lone]

Does this work?

Var A() as variant
A.add v1
A.add v2
A.add v3

[Eugene_Dakin]

@Greg_O_Lone

Does this work?

Although I don’t have any errors, it doesn’t appear to work. Thanks for the nice suggestion

For others who may search this in the future, here is a workaround that does not use Variants. (Thank you Mario!)

excel.Range(“A1:A4”).TextToColumns(excel.Range(“A1:A4”),1, 1,True, False, False, True, False, False, True)