Info Uuh

After opening the file, turn off the macro recorder and examine the recorded code:

Workbooks.OpenText Filename:="C:\sales.prn", Origin:=437, StartRow _ Workbooks.OpenText Filename:="C:\sales.prn", Origin:=437, StartRow:=1, _ DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(8, 1), _ Array(17, 3), Array(25, 1), Array(36, 1), Array(46, 1), Array(56, 9), _ Array(61, 9)), TrailingMinusNumbers:=True

The most confusing part of this code is the Fieldlnfo parameter. You are supposed to code an array of two-element arrays. Each field in the file gets a two-element array to identify where the field starts and the field type.

The field start position is zero-based; because the Region field is in the first character position, its start position is listed as 0.

The field type is a numeric code. If you were coding this by hand, you would use the xlColumnDataType constant names, but for some reason, the macro recorder uses the harder to understand numeric equivalents.

With Table 18.1, you can decode the meaning of the individual arrays in the Fieldlnfo array. Array(0, 1) means that this field starts 0 characters from the left edge of the file and is a general format. Array(8, 1) indicates that the next field starts 8 characters from the left edge of the file and is also general format. Array(l7, 3) indicates that the next field starts 17 characters from the left edge of the file and is a date format in month-day-year sequence.

Table 18.1 xlColumnDataType Values

Value Constant Used For

0 0

Post a comment