CODEWITHSUNDEEP
vbaexcel
Nancy Schroepfer
Nancy Schroepfer

Reputation: 57

How to understand VBA file IO?

I found this code used to read in numbers in notepad and import it into excel format, but what does it mean? Especially the Array() part.

Workbooks.OpenText Filename:=Path, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(3, 1), Array(5, 1), _ Array(11, 1), Array(12, 1), Array(13, 1), Array(19, 1), Array(20, 1), Array(25, 1), Array(30, 1), Array(35, 1), Array(40, 1), Array(45, 1), Array(50, 1), _ Array(55, 1), Array(60, 1), Array(65, 1), Array(70, 1), Array(75, 1), Array(80, 1), Array(85, 1), Array(90, 1), Array(95, 1), Array(100, 1), _ Array(105, 1), Array(110, 1), Array(115, 1), Array(120, 1), Array(125, 1), Array(130, 1), Array(135, 1), Array(140, 1), Array(145, 1), Array(160, 1)), _ TrailingMinusNumbers:=True

Upvotes: 1

Views: 436

Answers (1)

Heinzi
Heinzi

Reputation: 172408

  • Array(a, b, c, ...) simply creates a Variant array with the given elements. Thus, Array(Array(a, b), Array(c, d), ...) creates an array of arrays.

  • The FieldInfo parameter of OpenText with the xlFixedWith option specifies the field boundaries (MSDN):

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, as listed in the preceding table.

Thus, the list can be read as follows:

Field   StartsAtPosition  Type
-------------------------------
  1         0             1
  2         1             1
  3         3             1
  4         5             1
  5        11             1
...

where Type 1 specifies that this is a "General" column (constant xlGeneralFormat).

Upvotes: 1

Related Questions