Creating a flexible VBA conversion of TextToColumns-CodePudding

I want to make a piece of VBA code that converts text to columns flexible to any number of columns in a csv file:

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

I tried the following. After determining how may columns there are I want to use a variable 'txt' that replaces the Array declaration in the FieldInfo option:

txt1 = "Array("
txt2 = ", 1)"
txt3 = ", "
For i = 1 To N
    If i < N Then h = txt1 & i & txt2 & txt3
    If i = N Then h = txt1 & i & txt2
    txt = txt & h
Next i
txt = txt1 & txt & ")"

For N=3 this results into: txt = "Array(Array(1, 1), Array(2, 1), Array(3, 1))" However, the following code gives an error:

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

The error message is: "Method TextToColumns of class Range has failed".

How can I fix this?

CodePudding user response：

The issue is that you're building a string of text that contains the command to build an array, not an actual array.

The following function can be used to build an array of x elements:

Function createarray(x As Long)
    Dim arr, i As Long
    ReDim arr(0 To x - 1)
    For i = 0 To x - 1
        arr(i) = Array(i   1, 1)
    Next
    createarray = arr
End Function

Using the above, the following command:

FieldInfo:= Array(Array(1, 1), Array(2, 1), Array(3, 1))

could be swapped out for:

FieldInfo:= createarray(3)