EXCEL Text to Columns macro with a variable numberof Fields

Hi Guys,
I've go a question again about something that I can't seem to put together.

The code below works fine for the 41 columns and does exactly what it needs to do

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), _
    Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
    Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), _
    Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), _
    Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1)), _
    TrailingMinusNumbers:=True

What I am trying to figure out is how can y use variable list of fields for the FiledInfo

I have this routine in the macro that checks how many columns are in the first row of the importet text file

Dim headArrRow  As Variant, hCol As Integer, fInfoArrlist As Variant
headArrRow = Split(WS.Cells(1, 1), ",")
fInfoArrlist = ""
For hCol = LBound(headArrRow) To UBound(headArrRow)
    fInfoArrlist = fInfoArrlist & "Array(" & hCol & " ,1),"
Next hCol
fInfoArrlist = Trim(Left(fInfoArrlist, Len(fInfoArrlist) - 1))

The contents of fInfoArrList will vary in relation to the what the text file contains

[code]
For 6 colums it looks like this fInfoArrList= "Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)"
And more columns well more

My idea is that I use this as a variable (see the red text below) but it doesn't work, no error nothing it just doesn't do anything the data remains in one column

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

The question is how can this be made to work?

Hope my explanation makes sense
Thanks for looking and any ideas or tips are welcome.

Instead of building a string, why not ReDim an array the size you want and then loop through it making each entry in the array represent a column … much as you have done with the string. That way, you end up with an array. Hopefully you can use that in the text to columns.

Totally untested but an idea to play with.

Yes, you need an array whose elements are themselves arrays.

Thanks @TMS adn @rorya, the array, yes, that's tru a single array, I was confused by the Array(1,1) all I need then is one dimentional array .
I'm off to be now and will look and test tomorrow, I was making it more difficult than it really is.
Thanks again, will update tomorrow (if I get it done) and will also post the code snippet
Good-night

A 1D array of arrays to be specific.

@Marc L: I agree and this is the default setting, once I get that working I have a table that checks the ghearder name where it reads if it's a date or a general or specific text setting, thta's why I'm trying to figure it out
Tried array one D and 2D but it doesn't work

My last attempt as 2D array no go either

Dim headArrRow  As Variant, hCol As Integer, fInfoArrlist() As Variant
ReDim fInfoArrlist(1 To UBound(headArrRow), 2)
For hCol = LBound(headArrRow) To UBound(headArrRow) - 1
    fInfoArrlist(hCol + 1, 1) = hCol + 1
    fInfoArrlist(hCol + 1, 2) = 1
Next hCol
'*
WS.Activate
Columns("A:A").Select

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

Thanks anyway, will find another way

2D is wrong. You need a 1d array and each element of that is also a 1D array

Hi guys, thanks and i got it working, now only the finetuning but thanks

Dim headArrRow  As Variant, hCol As Integer, fInfoArrlist()
headArrRow = Split(WS.Cells(1, 1), ",")
ReDim fInfoArrlist(1 To UBound(headArrRow), 1)
For hCol = 1 To UBound(headArrRow)
    fInfoArrlist(hCol, 1) = 1
Next hCol
'*
WS.Activate
Columns("A:A").Select

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