Excel Type Library and Dolphin

Hi All

I posted recently on communicating with Excel from within a Dolphin app. I
have now produced a working system, but using the most indirect route, i.e.
using an IScriptControl to issue JavaScript commands to Excel. I failed
completely using the classes generated from the Excel type library by the
AXTypeLibraryAnalyzer. I have spent a little time trying to work out why
this method failed, and I have concluded that the generated classes are
incapable of working together; methods in class A which should generate an
instance of another class B instead generate an instance of IDispatch, and
so it is impossible to ever have an instance of class B. It looks as though
either the Excel type library is wrong or the AXTypeLibraryAnalyzer does not
interpret it correctly. The 'evidence' is below - rather lengthy, I'm
afraid, so apologies in advance.

A slightly simplified example of what I am trying to do is given by the
following JavaScript statements (which work exactly as expected, by the
way):

xl=new ActiveXObject("Excel.Application");
xl.visible = true;
wb = xl.workbooks.Open("c:\\x12a\\review2005\\MyWorkBook.xls",false);
ws = wb.worksheets(4);
co = ws.chartobjects;
ch = co(1).chart;
ch.seriesCollection(1).values = ws.range("m6:m275");

The intention should be fairly clear; open MyWorkBook.xls, find the first
embedded chart on worksheet 4 and give new values (i.e. y-values) to the
first series on that chart. The last three lines could obviously be made
into one using the dot notation, but it's easier to demonstrate the problem
this way.

Using the classes generated by the AXTypeLibraryAnalyzer, the Smalltalk
equivalent is:

xl := (Smalltalk at: #Xl_Application) new.

xl visible: 0 rhs: true.

wb := excel workbooks open: 'c:\x12a\review2005\MyWorkBook.xls' lcid: 0

ws := wb worksheets item: 4.

co := ws chartObjects: 0.

ch := (co item: 1) chart.

(ch seriesCollection: 1 lcid: 0) values: (ws range: 'm6:m275').

The equivalence of the two is fairly clear; the mysterious 0 arguments in
the St version are the d*****d lcid (locale identifier).



If I run the St version line by line in a workspace, it falls over at the
line 'co := ...', complaining that I am illegally assigning to the
ChartObjects property. Checking why this is by single stepping in the
debugger, I see that ws does not understand Xl_Worksheet>>chartObjects:, the
reason being that the class of ws is IDispatch, not Xl_Worksheet. The code
invokes the DNU behaviour of IDispatch, which treats a method like
chartObjects: as an assignment to the corresponding property. Tracing
through the source code, it seems that almost every method which should
generate an instance of Xl_Worksheet (like XlWorksheets>>item:, but also
Xl_Workbook>>activeSheet) in fact yields an instance of IDispatch. (The only
exception is XlRange>>worksheet.)



If I get round this (e.g. by using the IDispatch>>getProperty: route), the
thing falls over two lines later for a similar reason; co is not an instance
of XlChartObjects,as expected, but of IDispatch. Get past that, the next
problem is that (ch seriesCollection: 1 lcid: 0) is not an instance of
XlSeries, as expected, but of IDispatch again. And so it goes on.



As I said, I decided that this was no fun, so I switched to JavaScript,
which worked first time once I had remembered the right syntax. I found it a
pain sometimes having to write Smalltalk code to generate JavaScript code,
and the result is pretty ugly, but it is mostly wrapped up in a few methods,
so it's not too bad.



What is of more general concern is why this happens, and why it does not
always happen (for example, in the code above, 'wb worksheets' generates an
instance of XlSheets, as expected). It looks as though the
AXTypeLibraryAnalyzer is not correctly interpreting the type library, while
somehow JavaScript is getting it right.



If it's relevant, I am using Excel 2003; the type library is obtained from
the .exe file c:\Program Files\Microsoft Office\Office11\excel.exe.



Any comments gratefully received.



Peter Kenny

"Peter Kenny" <[hidden email]> wrote in message
news:[hidden email]...
> I posted recently on communicating with Excel from within a Dolphin app. I
> have now produced a working system, but using the most indirect route,
> i.e.
> using an IScriptControl to issue JavaScript commands to Excel. I failed
> completely using the classes generated from the Excel type library by the
> AXTypeLibraryAnalyzer. I have spent a little time trying to work out why
> this method failed, and I have concluded that the generated classes are
> incapable of working together; methods in class A which should generate an
...
 > ws := wb worksheets item: 4.

Try something like this instead:
=============
ws := (wb sheets item: 4) asImplType.
=============
or if that doesn't work try this:
=============
ws := (wb sheets item: 4) queryInterface: Excel_Worksheet.
=============
This should force the reference to be wrapped in the Smalltalk wrapper
class.  I can automate Excel from Dolphin using the ActiveX wizard generated
wrappers without any problem using this approach.  However I have not yet
had to work with charts.  In regard to the charts, make sure you have
generated wrappers for them.  It is possible that some chart components may
be in another ActiveX library.

Chris

"Christopher J. Demers" <[hidden email]> wrote in
message news:[hidden email]...
 =============
> ws := (wb sheets item: 4) asImplType.
> =============
> or if that doesn't work try this:
> =============
> ws := (wb sheets item: 4) queryInterface: Excel_Worksheet.
> =============
> This should force the reference to be wrapped in the Smalltalk wrapper
> class.  I can automate Excel from Dolphin using the ActiveX wizard
generated
> wrappers without any problem using this approach.  However I have not yet
> had to work with charts.  In regard to the charts, make sure you have
> generated wrappers for them.  It is possible that some chart components
may
> be in another ActiveX library.
>
Chris

Thanks for this. The use of asImplType gets me over several hurdles, down to
the last line of my example, but here it falls over before I have a chance
to use asImplType. The system rejects (ch seriesCollection: 1 lcid: 0), even
though the earlier steps have ensured that the class of ch is Xl_Chart, as
expected. The command Xl_Chart>>seriesCollection:lcid invokes the private
method Xl_Chart>>SeriesCollection:lcid:RHS:, which fails saying
SmallInteger(Xl_Chart) does not understand. I am even more flummoxed.

There is a further problem when I test the last part of this line, which
obtains the XlRange given by (ws range: 'm6:m275') and tries to assign it to
the 'values' property of the series. The XlRange does not seem to have the
right values; the inspector shows it has only the first value of the 270 in
the range. This links to Don Rylander's comments on my earlier post, about
constructing a SAFEARRAY, except that I am not trying to fill a range, but
rather to interpret the values in an existing range as a SAFEARRAY argument.

There is not  a problem about the chart components, by the way; these are
standard excel charts, and all the interfaces are in the Excel type library.

As to the general question about automation, I don't see how one can tell
when asImplType is to be used and when not. The comment for this method says
it is not used always because it slows things down. There seems no
alternative to trying each step of a calculation and using this whenever a
problem arises. Despite its ugliness, JavaScript seems a great deal easier.

Thanks again for your help. I still wonder whether the automation route is
really workable; it all looks like a collection of ad hoc fudges to get it
to work, and it just seems so fragile.

Peter

"Peter Kenny" <[hidden email]> wrote in message
news:[hidden email]...
Neither is, strictly speaking, wrong. Dolphin's type-library analyser is
correctly interpreting the information provided in the typelib, which
indicates that the "return" types are a generic IDispatch. That Excel's
typelib specifies types in an overly-generic manner is not "wrong", it is
just less useful than it might be in many cases. There could be cases where
it really needs to specify IDispatch because the types are not known
statically, but probably most of them could use a more specific type and
then the analyzer generated code would be more useful. Unfortunately the
automation object models of the office applications overuse VARIANT and
IDispatch. This is not universally true, however, and most object models,
especially those which started life a bit later, are more tightly defined
than those for MSO.

There is nothing to stop you modifying the generated methods to downcast the
IDispatch pointers to specific interface types. The most efficient and safe
way to do this is to #queryInterface: the correct interface type and return
that instead of the IDispatch answer. You can do it more generically by
inserting a #downCast message send (or #asImplType if the return value could
possibly be null) into the generated method. If you look at the IXMLDOMNode
class in the 'XML DOM' package you will see that it actually overrides the
#asObject method in order to perform an automatic downcast to specific DOM
node types of all IXMLDOMNode return values. Yes, it would be a useful
enhancement to be able to configure the AX Component Wizard to do this
automatically for cases like MSO. BTW: Any methods you modify should be
removed from the '** auto-generated **' category to prevent them being
overwritten in future.

>...
> What is of more general concern is why this happens, and why it does not
> always happen (for example, in the code above, 'wb worksheets' generates
> an
> instance of XlSheets, as expected). It looks as though the
> AXTypeLibraryAnalyzer is not correctly interpreting the type library,
> while
> somehow JavaScript is getting it right.
> ...

As I said, the information is not in the type-library to interpret. More
information is available at runtime and can be used to dynamically determine
the type. The DNU handling that Dolphin's IDispatch performs is deliberately
simplistic - it could do a better job and dynamically look up type
information that allows it to perform the correct operation in many more
cases, but the COM interop was primarily designed to be used through the AX
Component Wizard/analyser. Sometimes it is necessary to help out the
analyser where the type-library is lacking. We find it is often necessary to
extend the generated classes anyway if one wants them to participate in the
full richness of the Smalltallk object soup. For example you can often make
wrap up the collections in COM objects so that they are indistinguishable
from Smalltalk collections, but it is probably not feasible to generate such
wrappers automatically.

Regards

Blair

"Blair McGlashan" <[hidden email]> wrote in message
news:vRU5e.746$[hidden email]...

[snip]

> There is nothing to stop you modifying the generated methods to downcast
the
> IDispatch pointers to specific interface types.

What is not clear from any of the documentation is that, for Excel at least,
this is not only possible but absolutely /necessary/ to get a working
system. As i pointed out at fisrst, even to obtain such a basic thing as an
instance of Xl_Worksheet it is necessary to do this. Since Excel is used as
an example in the AXTypeLibraryAnalyzer, it would be helpful to give some
warning there.

> Yes, it would be a useful
> enhancement to be able to configure the AX Component Wizard to do this
> automatically for cases like MSO.
[snip]
> As I said, the information is not in the type-library to interpret. More
> information is available at runtime and can be used to dynamically
determine
> the type. The DNU handling that Dolphin's IDispatch performs is
deliberately
> simplistic - it could do a better job and dynamically look up type
> information that allows it to perform the correct operation in many more
> cases, but the COM interop was primarily designed to be used through the
AX
> Component Wizard/analyser.

I must agree that these enhancements would be useful. In fact, I think
without them the AXTypeLibraryAnalyzer generated interface for Excel is
unusable.

Sorry to be so niggly. I have now got round the difficulty by working with
JavaScript, so I no longer have a problem - but the time wasted trying to
get the interface to work still rankles.

Peter