TextToColumn or OpenText Automation

[lemo]

hi.
i am trying to automate splitting the text file in excel. my data comes as a text file with one big block of data, no delimiters, just one digit per field.
i figured that i need to use either Workbooks.OpenText command or Selection.TextToColumns commands to do the job, but in both cases i run into the problem that the Array sub-command is not dynamic. for example, something like this -

Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), 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)), TrailingMinusNumbers:=True

would work if my block is 10 and only 10 digits wide. can i make it dynamic instead, so that it would work for any number of columns (up to say 120)?

thanks,
len

 

[lemo]

looks like i figured it out. or, rather, found the answer online, as usual.
basically, all those FieldInfo arrays need to be defined as one array variable.
the code is something like this -

...
Dim fieldarray As Variant
ReDim fieldarray(1 To colstot)
For i = 1 To colstot
fieldarray(i) = Array(i - 1, 1)
Next i
ActiveWorkbook.Worksheets("Sheet1").Range("A1:A" & rowstot).TextToColumns _
Destination:=Range("B1:B" & rowstot), DataType:=xlFixedWidth, _
FieldInfo:=fieldarray, TrailingMinusNumbers:=True
...

l