APL and Excel ActiveX control 
Author Message
 APL and Excel ActiveX control


> > Communicating with Excel seems to be a perennial topic. Using Dyalog
> > 9 under Win2K, the OLE server passes arrays back and forth as
> > expected; but the MSOWC ActiveX control with the "same"
> > methods/properties only seems to understand scalars.

In case this is of interest to anyone, I'll summarize what I've learned
so far.

> There seems to be no serious documentation of the object model of the
> Spreadsheet Web Control on Microsoft's web pages. I only found a
> description of its capabilities at:
> http://www.*-*-*.com/

Actually I believe that's for the newer Office XP version.  There's an
analogous doc for the Office 2K version at

As you indicated, none of it is overly informative, but thanks for the

> >       ActiveSheet.(Range'A1:A2').Value <- 2 1 rho 11 22

> > It knows the correct shape of the range (2 rows), but the Value
> > property appears broken both for reading and writing.
> my gut feeling is that the MSOWC does not support array properties.

Reasonable conclusion, but according to the MS help docs it does (or
rather, it is supposed to) support arrays.

> One thing you can try
> is to try the same thing you are trying to do from APL in VB (or VBA).
> If you can do it there, then it could be that Dyadic's OLE engine needs
> a little tuning. If you can't do it from VB or VBA then, I'd say,
> there's no hope and you are forced to loop.

I thought so too, but Graeme at Dyadic support reminded me that VB
doesn't really need TypeLibs, so other languages are at a disadvantage.
Graeme then determined that the TypeLib for the Spreadsheet control was
in error and came up with a fix in one direction:

Using the SetPropertyInfo method you can correct the argument-passing
for a Range.Value `Put'.  It should be 'VT_ARRAY of VT_VARIANT'.
(VBScript had no problem, presumably since it never saw the TypeLib

Fixing `Get' seems to be quite another matter.  In APL or VBScript
Range.Value insists on returning only the top-left scalar of the
specified range.

So I looked around for alternatives to the Value property.  The
Spreadsheet CSVData property appeared promising, particularly since
Excel formulas are of no use in APL.  But it too is broken, though one
page at microsoft.com seems to try to characterize this more as a
feature than a bug.  8-[

Finally I found that the HTMLData property works in *both* directions
(though I only needed `Get').  Parsing the HTML to create an APL array
is rather klugey, but good enough for prototyping purposes.  (And it
sure beats looping!)

BTW there seems to be at least one other possibility:
http://www.*-*-*.com/ ;EN-US;q248822&
"The Spreadsheet Component passes Range arguments to custom functions
using the IXRangeEnum interface. IXRangeEnum is listed as a hidden
member in the Office Web Components Type Library (MSOWC.dll) and,
therefore, is undocumented in the Help for the Office Web Components
object model."

Undocumented hidden interface?!  Sounds a bit scary.  ;-/

> Sorry I cannot help you more.

Well you certainly helped me, in that you confirmed my suspicions and
saved me some time.  Thanks again.

No doubt I've missed something useful in the MS libs.  But the whole
exercise reminded me why I prefer open-source -- quality.  If it wasn't
for the occasional bright spot (like Dyalog APL!) I guess I wouldn't be
using proprietary software at all.


Joseph E. Burpee
GPG Fingerprint: 9188 AB4F A7B6 4AE5 9FB7  EF30 BE9B B1A2 9F6C BB8B

Wed, 16 Feb 2005 22:45:06 GMT  
 [ 1 post ] 

 Relevant Pages 

1. APL and Excel ActiveX control

2. CFW 2.0 and Microsoft Internet Control Pack Beta 2 ActiveX Controls

3. Excel activex, setting multiple cells in a column range

4. Strange behaviour with ActiveX invoke node and Excel

5. Closing Excel with ActiveX causes problem

6. Howto call a vi by activeX or DLL from Excel

7. import Excel Template into LV ActiveX container

8. Closing Excel with ActiveX causes problem

9. How to save then quit excel using activeX

10. Cannot open Excel using Activex

11. excel operation using activex

12. Excel-Activex Question for Alberto (or anyone else)


Powered by phpBB® Forum Software