Looking for Excel Automation Example Code

I'm looking for some sample code to  get started on Excel Autmation. I
intent to use Excel as an report output generator, but find this more
difficult than in other languages (Ruby/Python).

Best Regards,


Frank

Frank,
     Look in class AXTypeLibraryAnalyzer at example3, which gives a some
code using Excel 2000, which should give you a start.

    Ron
Frank wrote in message <[hidden email]>...
>I'm looking for some sample code to  get started on Excel Autmation. I
>intent to use Excel as an report output generator, but find this more
>difficult than in other languages (Ruby/Python).
>
>Best Regards,
>
>
>Frank
>

>>looking

Me too.

>     Look in class AXTypeLibraryAnalyzer at example3

I cant see the source of that and other examples, I think because I'm using
the eval version. It would really be a help. I am also trying to work out how
to use the excel control.  I'm current stuck on this method:

ExcelWorkbooks >> add: template lcid: lcid

When I've seen wrappers of this function in other languages is was usually
very simple, just Add with no parameters.

I dont have a clue what template is supposed to be. I've also been searching
all over the net without success trying to find a programming reference for
the excel control (i heard the excel help is supposed to have some doc but my
excel help doesnt work).

object-arts guys, any chance of letting me see some of that example code, of
all the things to hide :(

-alan

On Wed, 19 Dec 2001 20:10:03 -0000, "Ronald Hallam" <[hidden email]>
wrote:

Alan

You wrote in message news:C24hPOWJ8s=[hidden email]...
> >>looking
>
> Me too.
>
> >     Look in class AXTypeLibraryAnalyzer at example3
>
> I cant see the source of that and other examples, I think because I'm
using
> the eval version. It would really be a help. I am also trying to work out
how
> to use the excel control.  I'm current stuck on this method:
>
> ExcelWorkbooks >> add: template lcid: lcid
>
> When I've seen wrappers of this function in other languages is was usually
> very simple, just Add with no parameters.

That's because those are "optional" parameters. The type-library analyzer in
Dolphin 4 (the subsystem responsible for generating the wrapper classes, the
entirety of which, BTW, is written in Smalltalk and is visible in a fully
licensed copy of Dolphin) only generates one "high-level" wrapper method
for a COM method, which includes all arguments whether optional or not. This
decision was made because we wanted to avoid generating a mass of methods
for all the different combinations of optional parameters. In D5 the
analyzer will generate the simplest (i.e. no optional parameters) and most
complete (all optional parameters) versions of a method. Any others, you'll
have to fill in yourself. How you do this will depend on the argument types,
but typically you can pass 'nil' for the missing parameters. If the argument
type is 'VARIANT', then pass 'VARIANT unspecified'. In D5 this will be
pretty easy, since one has all the necessary code in the simplest/most
complete pair of generated methods, but with D4 you will have to manually
add any additional "convenience" method forms you want, working out how to
pass the optional parameters.

>
> I dont have a clue what template is supposed to be. I've also been
searching
> all over the net without success trying to find a programming reference
for
> the excel control (i heard the excel help is supposed to have some doc but
my
> excel help doesnt work).

Presumably you have looked at MSDN (http://msdn.microsoft.com)? There seems
to be a lot of relevant documentation on it there. I found some pretty
quickly that explains that the purpose of the optional 'template' argument.

>
> object-arts guys, any chance of letting me see some of that example code,
of
> all the things to hide :(

I've posted the examples below. There was no conscious decision to hide
them, they just happen to belong to one of the packages ('ActiveX
Automation' I think) which has encrypted source and which will therefore be
invisible in an eval.

Regards

Blair
-----------------------------
!AXTypeLibraryAnalyzer class methodsFor!

example3
 "Example using the Microsoft Excel 2000 (can be adapted for older
versions).
 This code should ideally be run line-by-line from a workspace.
  AXTypeLibraryAnalyzer example3
 "

 | excel books book range font arry caption |
 Smalltalk at: #ExcelLib ifAbsent: [ | xl |
   xl := AXTypeLibraryAnalyzer open: 'c:\Program Files\Microsoft
Office\Office\excel9.olb'.
   xl prefix: 'Xl'.
   xl generateConstantsPool.
   #(_Application Workbooks _Workbook Range Font) do: [:i | (xl at: i)
generate].
   xl reset].

 excel :=  (Smalltalk at: #Xl_Application) new.
 books := excel workbooks.
 book := books add: VARIANT unspecified lcid: 0.

 "Make the sheet visible - you'll lose focus"
 excel visible: 0 rhs: true.

 "Set the workbook caption"
 caption := 'Dolphin-VisualWorks Performance Comparison'.
 excel caption: caption.

 "Add a title in the sheet itself"
 range := (excel range: 'A1' cell2: VARIANT unspecified) value: caption;
yourself.
 (excel range: 'A2' cell2: VARIANT unspecified) value: '(266Mz P1, 96M,
Win98)'.

 "And set its font"
 font := range font.
 font bold: true; italic: true; size: 22.

 "Now insert some values"
 range := excel range: 'A4' cell2: 'F4'.
 range value: #('STIC Benchmark' 'Dolphin 2.1' 'Dolphin 3.01' 'VWNC 3.0'
'D2.1/VW' 'D3.0/VW').

 "Bit tricky to handle 2-D array, need some higher level help really"
 range := excel range: 'A5' cell2: 'A14'.
 arry := range value.
 #('Towers Of Hanoi' 'Alloc.' 'Array Write' 'Dictionary Write' 'Floating
Math'
  'Integer Math' 'OrderedCollection Iterate' 'OrderedCollection Write'
'String Compare' 'String Compare 2')
  keysAndValuesDo: [:i :s | arry elementAt: (Array with: i with: 1) put: s
asVariant].
 range value: arry.

 range := excel range: 'B5' cell2: 'D14'.
 arry := range value.
        #(   #(4715 10990 72791 7980 75870 14639 12617 21946 3981 7738)
"Dolphin 2.1"
   #(4615 11973 70180 8074 84630 14501 8709 20262 10964 18355) "Dolphin 3.0"
   #(678 8936 20520 3153 90170 14720 5090 8694 18659 18653))   "VWNC 3.0"
  keysAndValuesDo: [:j :a |
  a keysAndValuesDo: [:i :n | arry elementAt: (Array with: i with: j) put: n
asVariant]].
 range value: arry.

 "Add formulas for ratios; and format"
 (excel range: 'E5' cell2: 'E14') formula: '=B5/D5'; numberFormat: '0.00'.
 (excel range: 'F5' cell2: 'F14') formula: '=C5/D5'; numberFormat: '0.00'.

      "Add fancy formatting to our report."
 (excel range: 'A1:F14' cell2: VARIANT unspecified)
   autoFormat: ((Smalltalk at: #XlConstants) at: 'xlSimple' )
   number: VARIANT unspecified font: VARIANT unspecified
   alignment: VARIANT unspecified border: VARIANT unspecified pattern:
VARIANT unspecified
   width: VARIANT unspecified.

        "Print Preview"
 "(excel range: 'A1' cell2: 'F14') printPreview: true."

        "Save the active workbook."
 book := excel activeWorkbook.
 book invokeId: (book idOfName: 'SaveAs') with: (File composePath: '' stem:
caption extension: 'xls').

 excel quit! !
!AXTypeLibraryAnalyzer class categoriesFor: #example3!examples!must
strip!public! !

!AXTypeLibraryAnalyzer class methodsFor!

example1
 "Exercise ITypeLib a bit."
 | tlb |

 tlb := AXTypeLibraryAnalyzer open: 'stdole2.tlb'.

 "Display the names of the members of the library"
 tlb names.

 "Ask whether particular members are present in the library."
 tlb isName: IUnknown name.
 tlb isName: ITypeLib name.

 "Answer all the documentation for a particular member (e.g. IUnknown)"
 tlb documentationAt: 4.
 (1 to: tlb typeInfoCount) collect: [:i | tlb documentationAt: i].

 "Answer an ITypeInfo for a particular member."
 tlb typeAnalyzerAt: 4.
 (1 to: tlb typeInfoCount) collect: [:i | tlb typeAnalyzerAt: i].

 "Get the library attributes."
 tlb attributes.

 "Now try some higher level enumerations"
 tlb coclasses.
 tlb typedefs.
 tlb structs.
 tlb enums.
 tlb interfaces.
 tlb dispinterfaces.
 ! !
!AXTypeLibraryAnalyzer class categoriesFor: #example1!examples!must
strip!public! !

!AXTypeLibraryAnalyzer class methodsFor!

example2
 "Example using the Microsoft Scripting Runtime.
 One of the main purposes of the Scripting Runtime is to provide an
object-oriented file
 system model, and we exercise a very small part of its capabilities here.
  AXTypeLibraryAnalyzer example2
 "
 | fs cdrive croot windir systemdir l |

 "First lets get the FileSystem object, we might need to generate some
wrappers to do so"
 fs := (Smalltalk at: #IFileSystem ifAbsent: [ | tlb |
   tlb := AXTypeLibraryAnalyzer open: 'scrrun.dll'.
   "We'll forgo the prefix on all the generated names"
   tlb prefix: ''.
   "Generate the wrappers for the type library interfaces"
   tlb generateInterfaceWrappers.
   Smalltalk at: #IFileSystem]) createObject: 'Scripting.FileSystemObject'.

 "Now we can test it out a bit by finding the root of C"
 cdrive := fs drives item: 'C'.
 croot := cdrive rootFolder.

 "And then from there by locating the system folder"
 windir := OSVERSIONINFO current isNT
    ifTrue: [croot subFolders item: 'winnt']
    ifFalse: [croot subFolders item: 'windows'].
 systemdir :=  windir subFolders item: 'system32'.

 "A better way to get the system folder is:"
 systemdir := fs getSpecialFolder: ((Smalltalk at: #ScriptingConstants) at:
'SystemFolder').

 "Display the contents of the system folder (files only) in a list box
(might take a while)"
 ListPresenter show list: systemdir files contents
! !
!AXTypeLibraryAnalyzer class categoriesFor: #example2!examples!public! !

Blair,

>That's because those are "optional" parameters...
>...
I appreciate your crystal clear answers.

>Presumably you have looked at MSDN (http://msdn.microsoft.com)? There seems
>to be a lot of relevant documentation on it there.
>I found some pretty quickly that explains that the purpose of the optional 'template' argument.
 
I'm humbled. Yes, all searchs roads led there, I did not find a formal api
reference. (Maybe I should have been thinking 'VB/A' not 'Activex control').
Going back and doing a google site search I just found

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlmscProgrammaticIdentifiers.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D3/S5A341.asp

with a section under 'Excel' and a better one under 'Office'

did you find something better?.

>I've posted the examples below. There was no conscious decision to hide
>them, they just happen to belong to one of the packages ('ActiveX
>Automation' I think) which has encrypted source and which will therefore be
>invisible in an eval.

Yes i understood that on reflection. Thanks very much for the example3.

> In D5 this will be pretty easy, since one has all the necessary code in the simplest/most
>complete pair of generated methods.

Looking forward to that, how far off is it?

regards
alan

Alan

You wrote in message news:[hidden email]...
>... I did not find a formal api
> reference. (Maybe I should have been thinking 'VB/A' not 'Activex
control').
> Going back and doing a google site search I just found
>
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/htm
l/xlmscProgrammaticIdentifiers.asp
> and
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/ht
ml/output/F1/D3/S5A341.asp
>
> with a section under 'Excel' and a better one under 'Office'
>
> did you find something better?.

I think I was looking at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/htm
l/xlmthAddWorkbooksObj.asp, but it was on a locally installed MSDN, rather
than via the web site.

It's rather typical, unfortunately, that MS choose to document a supposedly
language independent COM component as if it is only usable from VB/A.

> ...
> > In D5 this will be pretty easy, since one has all the necessary code in
the simplest/most
> >complete pair of generated methods.
>
> Looking forward to that, how far off is it?

There is no firm release date yet, but if it isn't in Q1 2002 then I will be
retiring from the computing profession and signing up with the Caldey Island
monastery as a novice :-).

Regards

Blair

I'm going to hijack this thread since it's on the same subject.

I generated wrappers for the 'Microsoft Excel 9.0 Object Library'. This worked
just fine for automating external Excel spreadsheets.

I am having less success in trying to use imbedded spreadsheets. In the
Active-X control browser I open 'Microsoft Office Spreadsheet 9.0'. (I'm not
clear on how this is related to the library above).  Inspecting this control
from the browser, the typeInfo is TKindInterfaceAnalyzer('IWebCalc'). I'm not
sure what was the right (ie wizard) way to get this control wrapped, but I
sent #generateWrapper to it and got the classes OWCIWebCalc (which is the
spreadsheet),  OWCPane, OWCRange, OWCTitleBar and OWCWorksheet but methods
only for OWCIWebCalc. It looks like there's a ton more OWC classes in this
library.

Next, I sent #activeSheet to the IDispatch for the OWCIWebCalc, and got an
IDispatch representing the OWCWorksheet (btw is there any way to 'cast' or
generate results like this into the right class), and generated it's methods.
I also navigated to the analzer for OWCRange and generated it's methods.

Now if I send  range: 'A1' cell2: 'A1' to the OWCWorksheet IDispatch, I get
the error 'Member not found' and that's where I'm currently stuck.  Not sure
what a 'member' is.

Any help or examples would be appreciated.

-alan r

Alan

"ar" <[hidden email]> wrote in message
news:[hidden email]...
> I'm going to hijack this thread since it's on the same subject.
>
> I generated wrappers for the 'Microsoft Excel 9.0 Object Library'. This
worked
> just fine for automating external Excel spreadsheets.
>
> I am having less success in trying to use imbedded spreadsheets. In the
> Active-X control browser I open 'Microsoft Office Spreadsheet 9.0'. (I'm
not
> clear on how this is related to the library above).

I believe it is one of the "Office Web Controls" (or something like that). I
seem to recall that these are cut down versions of the office apps designed
to be embedded in HTML pages, but I may be wrong.

>...Inspecting this control
> from the browser, the typeInfo is TKindInterfaceAnalyzer('IWebCalc'). I'm
not
> sure what was the right (ie wizard) way to get this control wrapped, but I
> sent #generateWrapper to it and got the classes OWCIWebCalc (which is the
> spreadsheet),  OWCPane, OWCRange, OWCTitleBar and OWCWorksheet but methods
> only for OWCIWebCalc. It looks like there's a ton more OWC classes in this
> library.

That's what one would expect. You asked the analyzer to generate only the
IWebCalc interface. In so doing it may come across other interfaces that are
referenced, and so it creates stub classes for these as placeholders. You
can manually generate the other interfaces you need (as it appears you did
for OWCRange), or you can access the type-library analyzer from an interface
analyzer and work from there, or, easiest, open the AX Component Wizard and
generate from there. If you open it with the component displayed in the AX
Control Browser you should find an entry in the list of open typelibs that
appears on the first page of the wizard.

> Next, I sent #activeSheet to the IDispatch for the OWCIWebCalc, and got an
> IDispatch representing the OWCWorksheet...

Unfortunately a lot of the MSOffice interfaces return IDispatch where they
should return something more specific, but...

>... (btw is there any way to 'cast' or
> generate results like this into the right class), ...

You can "downcast" these implicitly by sending the IDispatch the #downCast
message, or by using an explict #queryInterface: <interface class>.

>...and generated it's methods.
> I also navigated to the analzer for OWCRange and generated it's methods.
>
> Now if I send  range: 'A1' cell2: 'A1' to the OWCWorksheet IDispatch, I
get
> the error 'Member not found' and that's where I'm currently stuck.  Not
sure
> what a 'member' is.

"Member" has a similar meaning in Automation terminology to its meaning in
C++, i.e. a member function (method) or member variable (property/instance
variable).

If you go through IDispatch, you are effectively working through
#doesNotUnderstand: processing. Dolphin attempts to invoke the "member", but
it doesn't know whether you are attempting a property "get", property "put",
or normal method invocation (Smalltalk does not distinguish between these
different operations, everything being a message send). Therefore Dolphin
attempts to invoke the member in some sensible default way, making a guess
at the type of member. Often this works, sometimes not. This could be made
more intelligent by making use of the type library (when available), but it
is better to go through the specific generated interface.

Regards

Blair

On Tue, 22 Jan 2002 11:40:18 -0000, "Blair McGlashan" <[hidden email]>
wrote:

>I believe it is one of the "Office Web Controls" (or something like that). I
>seem to recall that these are cut down versions of the office apps designed
>to be embedded in HTML pages, but I may be wrong.

No, that looks to be it. There's a surprising amount of stuff there (125
classes)

>>I'm not sure what was the right (ie wizard) way to get this control wrapped

>If you open it with the component displayed in the AXControl Browser you should find an entry in the list of open typelibs that appears on the first page of the wizard.

yes, exactly what happened, I missed them earlier because either I didnt have
the control open in the browser, or the name threw me off (thinking XL not
webthingy)

>Unfortunately a lot of the MSOffice interfaces return IDispatch where they
>should return something more specific, but...

at some point, (maybe only after some wrapper generations AND restarting the
image, if that makes sense), I started getting the right types in return
values instead of IDispatch.

Now I've got imbedded and external  spreadsheets working and partly integrated
into mvp (though it still needs work, I picked a hard one to learn mvp on :)

>member, downcast

Thanks for filling in a little more of the ActiveX puzzle.

regards,
Alan