TCOM Excel: learning COM...

[t.me...@c-x-r.com]

Hi,

I've recently started using TCOM on a very basic level but I would
like to understand it better so that I can increase (and improve) the
number of things that I can do with it. I was wondering if anyone
could point me in the right direction.

I use TCOM to parse data from TCL to a Excel spreadsheets (I've
managed to hack my way through to a working solution based on the
various examples available in the TCL Wiki and some posting from this
news group).

The manual mentions that TCOM accesses Exel through COM objects. My
basic problem is one of not knowing anything about COM...

Could anyone please advise on a book/tutorial that I can use as a
staring point to understand how to control Excel from within a TCL
script through TCOM? I don't necessarily want to have to learn the
whole COM infrastructure, so information relevant to TCL -> TCOM ->
Excel would be greatly appreciated.

Thanks
TM

[Alexandre Ferrieux]

FWIW, I share your approach, not wanting to invest too much in COM,
while aiming for interesting interaction.
The single best answer I know is the Comet browser by Tom Poindexter.
It uses COM's built-in "auto-discovery" capabilities (IUnknown) to let
you find, and interactively play with, each and every exposed class's
methods and properties. It does all this in a nice GUI with a very
handy handle-keeping policy (you're asked to give a name to objects
you want to keep around, so they stay available in a very intuitive
list on the left pane).

When you're attacking a COM API, basically all you need is the name of
the main class. If you don't know it you can still guess and find it
because Comet can be asked to display _all_ COM classes in
alphabetical order.

Otherwise, a very efficient path is to google for the thing you're
after and concentrate on 'VB API examples'. In fact these are working
COM examples with tiny amount of VBish syntax above; translating to
tcom syntax is trivial.

FWIW, I did exactly this to automate QualityCenter, never opened a COM
book, and now have a working daemon that bridges between the QC and
opensource-bugtracker worlds.

Good luck (and don't hesitate to come back with specific questions if
the above doesn't work as claimed).

-Alex

[t.me...@c-x-r.com]

Hi Alex,

Thanks for the suggestion - I'll certainly look into it.

One of the things that I didn't ask in my 1st posting was what the
relationship (if any) between COM and Visual Basic for Applications
is.

I came across the following book the other day:
'Brilliant Microsoft Excel 2007 VBA and Macros'

This book takes you through generating scripts in VBA (for Excel) and
I thought 'if only I could embedded this in TCL/TCOM...'

(Showing my total ignorance here) Can I re-use (aspects of?) a VBA
script in TCL/TCOM?

Thanks
TM

[Alexandre Ferrieux]

Yes, that was the meaning of my sentence "... these are working

COM examples with tiny amount of VBish syntax above; translating to

tcom syntax is trivial" ;-)

Take one of them, start an interactive wish, do [package require
tcom], then

set obj [::tcom::ref createobject apiname.classname]
$obj somemethod someargs

You'll see the mapping is really straightforward.

-Alex

>
> Thanks
> TM

[Larry W. Virden]

I would just like to encourage participants and observers of this
thread to consider adding information to http://wiki.tcl.tk/ as you
learn to do more with tcom and com. In particular, if there are
examples of automating spreadsheets - or vendor products such as
QualityCenter - which can be pointed to as examples of Tcl automation,
this would provide excellent resources to others trying to achieve the
same sort of thing. It might even provide the basis for an interesting
paper at a conference somewhere...

[Georgios Petasis]

O/H Larry W. Virden έγραψε:

Since I have done some work in excel from tcl, you can find an itcl
class I have written for an application here:

http://www.ellogon.org/~petasis/tcl/Excel.tcl

Besides the fact that most of the code is application specific, there
are lots of functions that do interesting things, like
opening/saving/creating spreadsheets, setting/reading cells, colouring
cells, locking the spreadsheet, etc.
Just look into the code and use whatever you need. And it is a good idea
if anybody can transfer the useful stuff into the wiki, or in
tcom/tcllib library...
(I am really sorry, but my free time is very limited to do it myself...)

George

[Paul Obermeier]

You may also take a look into my little Tcl extension TcomExcel, which
offers some basic Excel functionality in an easier to use fashion than
directly using the Tcom functions.
It contains some example scripts, too.

Go to http://www.posoft.de/html/extTcomExcel.html

Paul