| Mark Seger 2005-06-08, 8:58 am |
| I've been playing with the perl ole interface to talk to excel. Very
 . 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
the everything else is in VB - perhaps an opportunity for some
documentation enhancement? In any event 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 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?
If anyone is interested, the way this 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
|