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
The contents of fInfoArrList will vary in relation to the what the text file containsDim 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))
[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
The question is how can this be made to work?'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
Hope my explanation makes sense
Thanks for looking and any ideas or tips are welcome.
Bookmarks