0

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?

Eijte Foekens
  • 3
  • 2

1 Answers1

0

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)
CLR
  • 11,284
  • 1
  • 11
  • 29
  • A working solution (although I would use "x as Integer" in the Function createarray)! thanks! – Eijte Foekens Feb 18 '22 at 13:56
  • Check out https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long for some info on Int vs Long – CLR Feb 18 '22 at 14:17
  • Glad you have a solution. If you're happy with it, don't forget to mark it as an answer. – CLR Feb 18 '22 at 14:18