Re: Newbie: Accessing an Excel worksheet from a Collection

On 31/07/04 21:05, "Keith McAlpine" <keith_mcalpine1@xxxxxxxxx> wrote:

> Steven,
>
> Thanks for the tip. Unfortunately the Select method also gave me problems!
> The Item is a *property* of the Sheets collection, so eventually I got:
>
> mySheet = (DispatchPtr) sheets.get("Item", myWorksheetName);
>
> to get the correct results.

There are cases when you will find the *property* variation of Item where
get works but invoke does not.  I am surprised that Select gave you problems
though.  Can you tell me what error(s) occurred?

> The weird this is that for control objects on a worksheet (from a Shapes
> collection), the invoke method on an item works fine.

That's because Item for Shapes is a method not a property.

> Is there somewhere that clarifies the usage of the get, getObject, invoke
> and getN methods? I had thought that, given a DispatchPtr object:
>
> get() was for retrieving a property from it that was a primitive type;
> getObject()  was for retrieving a property from it that was another
> DispatchPtr; and
> invoke() was for invoking a method call on the object.

You are right they are, it is.  That's why get("Item",...) works for the
Sheets collection and invoke("Item",....) does not.  Item in the Sheets
collection is a property not a method.  Somewhere in the internal workings
of VB calling the property with a reference (either a number or a sheet
name) will give you the desired sheet.  So the interface is called a
property (which in Java would be called a field) but the behavior is like a
method.  I am not sure which of the semantics is best but the fact that
Microsoft can't themselves decide sometimes using the property interface,
sometimes the method means that you cannot use the signature *Item* to imply
which interface is appropriate across Office API's.  You must carefully look
at the Microsoft documentation.

A word about the documentation is that Microsoft seems to be moving it
(again).  The old links to the MSDN VB API still exist but there now exists
a new site for "Microsoft Office Developer Center" in which I cannot find
the VB references.  So if you need the old links let me know.  Jawin
Typebrowser is also indispensable for discovering viewing the
characteristics of a particular object.

> So, I have got my functionality working, but it feels more like trial and
> error rather than based on some consistency.. or maybe that's Excel for
> you :)

I think most people have that opinion of Microsoft; arbitrary.  As Martin
said POI is an excellent project for reading and creating Excel files.  It
takes a different approach by reading/writing the information from/to the
file rather than using COM.  Limitations to POI are that it only supports
Excel and to some limited extent Word.  I (personally) find that Jawin
allows me to work with all the Office applications simultaneously and is
faster than POI.  The challenge to Jawin will be at what rate will Microsoft
move away from COM as the principal programming language.  Something I have
only heard rumored.

> Thanks
>
> Keith

Steven