Each element of the fieldinfo array consists of two dimensional array.

Array(77,5) for example.

You can look in VBA's help for texttocolumns and you'll see something like this
for the second number:

XlColumnDataType can be one of these XlColumnDataType constants.
xlGeneralFormat. General
xlTextFormat. Text
xlMDYFormat. MDY Date
xlDMYFormat. DMY Date
xlYMDFormat. YMD Date
xlMYDFormat. MYD Date
xlDYMFormat. DYM Date
xlYDMFormat. YDM Date
xlEMDFormat. EMD Date
xlSkipColumn. Skip Column

(I think in earlier versions of the help, they actually gave the numbers instead
of xl's constants):

1 xlGeneralFormat. General
2 xlTextFormat. Text
3 xlMDYFormat. MDY Date
4 xlDMYFormat. DMY Date
5 xlYMDFormat. YMD Date
6 xlMYDFormat. MYD Date
7 xlDYMFormat. DYM Date
8 xlYDMFormat. YDM Date
10 xlEMDFormat. EMD Date
9 xlSkipColumn. Skip Column

Copied from the help:
You can use xlEMDFormat only if Taiwanese language support is installed and
selected. The xlEMDFormat constant specifies that Taiwanese era dates are being
used.

And the first column is the position in the text (but start counting at 0).

So Array(Array(0, 1), Array(4, 2))
would start at the first position and treat it as General.
(actually the first 4 characters would go into that cell. The field goes until
the next field starts or until the end of line.)

Then position 5 (we started counting at 0) to the next field would be treated as
text.



sals wrote:

Can someone explain to me what the numbers represent, I try to count
it from the data, doesn't look like (rows, colums)

Workbooks.OpenText Filename:="C:\ACTEXTR0.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
_
9), Array(16, 1), Array(22, 1), Array(24, 1), Array(26, 1),
Array(29, 9), Array _
(31, 1), Array(73, 9), Array(77, 5), Array(85, 1), Array(92,
1), Array(103, 1), _
Array(115, 9), Array(121, 1), Array(146, 1), Array(171, 1),
Array(381, 1))
ChDir "C:\"

wrote in message . com...
I have a procedure that loads a 2D dynamic excel range inot a VBA
array.

Range("xlEntireXlArray").Activate
With Range("xlEntireXlArray")
ExcelRowCount = .Rows.Count
ExcelColumnCount = .Columns.Count
UploadArray = .Resize(ExcelRowCount, ExcelColumnCount)
End With

The VBA array will always represent the dynamic excel range.

What if I want more control over the columns with the excel range that
actually get loaded into the array. Assume the excel range is 10 rows
by 15 columns. But I do not want all 15 columns in the VBA array. If
I only wanted columns 1-5, and 7 (6 columns) loaded into the array, is
it possible to load these columns individually? How would you write
the 6 different load statements? I am not looking for a form here
that gives me the option of picking columns. I will want to do this
manually with code.

Secondly when I now pull these 6 columns into my VBA array, I want the
second dimension of my VBA array to be larger than the number of
columns I pulled in. Say I want it to be 20 elements across instead
of just 6. Do I just simply dimension it for 20? I want it to be
large because I want to do some additional calcs and store the values
in the VBA array.

TS

--

Dave Peterson