Moving ExcelMacro to Access

[tilamok]

Hi All

I have the following code in an excel macro and I wish to move it all to Access using specsfiles. However I can't make heads or tails as to how this array code works. It seems to be doing an array of an array.
Also 1st it uses (0,9) then (1,1) then (8,1), please help me interpret how this works.

Many Thanks


Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(1, 1), Array(8, 1), Array(15, 1), Array(17, 1), _
Array(18, 1), Array(20, 1), Array(21, 1), Array(36, 1), Array(37, 1), Array(52, 1), Array( _
57, 1), Array(58, 1), Array(60, 1), Array(62, 1), Array(64, 1), Array(71, 1), Array(78, 1), _
Array(81, 4), Array(89, 1), Array(90, 1), Array(91, 1), Array(92, 1), Array(98, 1), Array( _
99, 2), Array(103, 2), Array(115, 2), Array(127, 2), Array(153, 2), Array(185, 2), Array(217, 2), _
Array(249, 2), Array(269, 2), Array(278, 4), Array(286, 4), Array(294, 1))

[Bob Phillips]

It is just breaking a string up into differnt cells based upon known break character positions. The arrays govern those spslit point.

You would have to write some code to emulate breaking the text out into sub-texts.

[p45cal]

Taking the internal arrays:
Array(0, 9), Array(1, 1), Array(8, 1)
The first number of each (0,1,8) is the starting character number of the original string, counting from the left starting at 0. How long each field is is worked out from the beginning of the next field (and if there isn't one, the remainder of the original string).
The second number in each (9,1,1) tells excel how to format/parse each field according to:
1 General
2 Text
3 MDY date format
4 DMY date format
5 YMD date format
6 MYD date format
7 DYM date format
8 YDM date format
9 Column is not parsed
10 EMD date format

So the above says:
1.Don't import the first character (no field)
2.First field:Import characters 1 to 7 (from the two red numbers above) as General format
3.Next field:Import rest of string as General format. (but your example goes on with more fields here instead)

[tilamok]

Pascal,
That's super, never thought of interpreting it like that

Many Thanks



Taking the internal arrays:
Array(0, 9), Array(1, 1), Array(8, 1)
The first number of each (0,1,8) is the starting character number of the original string, counting from the left starting at 0. How long each field is is worked out from the beginning of the next field (and if there isn't one, the remainder of the original string).
The second number in each (9,1,1) tells excel how to format/parse each field according to:
1 General
2 Text
3 MDY date format
4 DMY date format
5 YMD date format
6 MYD date format
7 DYM date format
8 YDM date format
9 Column is not parsed
10 EMD date format

So the above says:
1.Don't import the first character (no field)
2.First field:Import characters 1 to 7 (from the two red numbers above) as General format
3.Next field:Import rest of string as General format. (but your example goes on with more fields here instead)