Setting Arrays in VB using layout in spreadsheet

[jyaeger]

I have a collection of data files that have their own unique layout and are changing constantly

What I was trying to do was to take the layout from the spreadsheet where it defines the record layout and create a string that defines the layout each time. I have another macro that sets up the formula to define each indivdual array (="Array("&D7-1&VLOOKUP(E7,$G$1:$H$5,2,0)). Then VB creates the complete Array by stringing each record together.

MyArray = ActiveCell.Value
ActiveCell.Offset(1, 0).Select

Do Until IsEmpty(ActiveCell.Value)

MyArray = MyArray & "," & ActiveCell.Value
ActiveCell.Offset(1, 0).Select


Loop

MyArray = "Array(" & MyArray & ")"

ActiveCell.Value = MyArray
ActiveCell.Offset(1, 0).Select

Workbooks.OpenText Filename:= _
"\\Path\Filename.txt", Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= "" & MyArray,TrailingMinusNumbers:= True

I get the always helpful Runtime 1004 error saying the method of opening the workbook object has failed.

Can I set the array this way? When I step through and look at the value held in MyArray, it looks fine (albeit a string)

Thanks for your help

Jeff

[Loz]

I think the the code is getting confused as to what is an array and what is text. When opening a text file on a fixed width basis, microsoft's help notes the following...

If the source data has fixed-width columns, the first element in each two-element array specifies the position of the starting character in the column (as an integer; character 0 (zero) is the first character). The second element in the two-element array specifies the parse option for the column as a number between 0 and 9...."

The parse options are as follows...

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

Therefore, you should be supplying a 2-dimensional array with column 1 set to the starting text position, and column 2 set to the format you're expecting, with as many rows as there are 'fields' in the data set.

The following three lines should capture the array of values from your worksheet and obviate the need for the existing lines up to the Workbooks.OpenText item -

Dim MyArray()
ReDim MyArray(Range(ActiveCell, ActiveCell.End(xlDown)).Rows.Count, 1)
MyArray = Range(ActiveCell, ActiveCell.End(xlDown).Offset(0, 1)).Value

This assumes the data in the worksheet is something like the following -

00 0
20 0
35 3
40 0
etc.

So MyArray(2,1) would pick up the value 20 as the character starting position for the 2nd field, and MyArray(3,2) would pick up the value 3 representing a 'DMY' item in the 3rd field (which starts at character 35) , and so on.

Then, the parameter FieldInfo:= can be set simply to FieldInfo:= MyArray