excel, vb arrays and perl

[Mark Seger]

I've been playing with the perl ole interface to talk to excel. Very
cool. The problem I'm having is virtually all the documentation on how
to do it in perl is too simplistic for anything more sophisticated and
everything else is in VB. The good news is I've managed to figure out
how to map from one syntax to the other except for VB arrays.

Specifically, I want to read in a text file that has space separated
field and the first one is in date format. To do everything but specify
the date in perl one simply does:

$Book = $Excel->Workbooks->OpenText({
Filename=>$filename, ConsecutiveDelimiter=>1, Space=>1,});

and it works like a champ. However to tell Excel the format of specific
fields, you need to add "FieldInfo" and that is defined as an array of
2 element arrays. I've tried a number of things but to no avail.
Anybody know how?

The way this statement is captured as a macro in Excel looks like:

Workbooks.OpenText Filename:= _
"C:\mjs\excel-perl\20050601-cx01-slab-s-days1.txt",
Origin:=437, StartRow:= 1,
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array(Array(1, 5), Array(2, 1),
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1),
Array(11, 1)), TrailingMinusNumbers:=True

but I don't need to set all the fields as most of them are already
defaulted the way I want them.

-mark