Excel COM, ActiveX ... still stuck
Günther Schmidt
I'm still stuck.
So I've run the ActiveX Wizzard on Excel (version 11) and have a HUGE
amount of classes.
They're in an extra package (nice) but how to go about using them I just
don't know.
My initial intent was just to extract the data row by row out of the
Excel file(s) into my application but I've just hit a wall here.
I should think my problem would be rather common, there must be others
who have done this before.
Please help (again)!!!
Günther
Another question:
I'm running XP with Office 2003. Once I managed to write this
application would it also run on a machine with an earlier version of
Excel installed?
I hadn't intended to use any of Excels latest features but rather very
basic operations i.e. what is this cell and such.
Bruno Buzzi Brasesco
IDispatch #example3
IDispatch #example4
Sorry, but i do not have Excel "names" ('ActiveWorkBooks', etc).
Regard Bruno
Bill Schwab
> Sorry everyone,
No worries.
> I'm still stuck.
>
> So I've run the ActiveX Wizzard on Excel (version 11) and have a HUGE
> amount of classes.
> They're in an extra package (nice) but how to go about using them I just
> don't know.
That's part of why I give you my stock advice re Office Automation.
More granular components with good IDL deserve no less than a generated
package; Office is another matter.
Buy VBA for Dummies. IIRC, there is a full edition and a smaller one
with a CD. For Word, I made extensive use of the info on the CD.
> My initial intent was just to extract the data row by row out of the
> Excel file(s) into my application but I've just hit a wall here.
>
> I should think my problem would be rather common, there must be others
> who have done this before.
Sorta. In my case, it was Word. I have always been able to move Excel
out of the way. I use the OpenOffice spreadsheet for project costing,
etc., and have no need to move that data. When data comes to me in
Excel format, I usually just copy it to another format and go from
there. Eventually I will end up writing a wrapper (unless you beat me
to it<g>), and then would probably just pull data directly from Excel
when appropriate. For now, it does not arise often enough to justify
the work.
You are welcome to clone my Word Automation package for Excel. It will
take some doing, but I genuinely believe you will be better off doing it
that way.
Have a good one,
Bill
--
Wilhelm K. Schwab, Ph.D.
bi...@anest4.anest.ufl.edu
Don Rylander
"Günther Schmidt" <gue.s...@web.de> wrote in message
news:41d0...@news.totallyobjects.com...
> Sorry everyone,
>
> I'm still stuck.
>
> So I've run the ActiveX Wizzard on Excel (version 11) and have a HUGE
> amount of classes.
> They're in an extra package (nice) but how to go about using them I just
> don't know.
>
> My initial intent was just to extract the data row by row out of the Excel
> file(s) into my application but I've just hit a wall here.
using the IDispatch interfaces with #getProperty: (or #getProperty:item:)
for properties, and #invoke: for methods.
I usually just start an Excel session, get the workbooks collection, open
the workbook, select the data, then copy it to the clipboard and work with
it as an array of lines. The columns are separated by tabs. The code is
something like this:
xl := IDispatch>>createObject: 'Excel.Application'.
xl setProperty: 'visible' value: true asParameter. "if you like to see what
you're doing"
theWorkbook := (xl getProperty: 'workbooks') invoke: 'open' with: 'my excel
file.xls'.
rangeArgs := Array with: 'a:p' asBSTR with: VARIANT unspecified. "Your
range may vary..."
range := theWorkbook getPropertyId: 197 "range" withArguments: rangeArgs.
"must be a 2-arg array"
range invoke: 'select'; invoke: 'copy'.
lines := Clipboard current getText trimBlanks lines.
xl setProperty: 'cutCopyMode' value: 1. "cancel the copy mode"
As I recall, if you try to do much in Excel, it involves lots of arrays of
Variants. Very annoying to work with.
Let me know if this points you in the right direction,
Don
Bill Schwab
> I end up doing a lot of ad hoc stuff with Excel, and I've taken to just
> using the IDispatch interfaces with #getProperty: (or #getProperty:item:)
> for properties, and #invoke: for methods.
That is precisely my approach with Word, except that I put simple
wrappers around the interfaces with the goal of making it easier "next
time". Do you think a similar (perhaps even combined/reused) class
hierarchy would be of any value to you?
Don Rylander
"Bill Schwab" <bsc...@anest.ufl.edu> wrote in message
news:cqqn8m$ghc$1...@spnode25.nerdc.ufl.edu...
[...]
> That is precisely my approach with Word, except that I put simple wrappers
> around the interfaces with the goal of making it easier "next time". Do
> you think a similar (perhaps even combined/reused) class hierarchy would
> be of any value to you?
generate them first, then tidy them up into something useful? I originally
did that with Excel a couple of years ago, but a fair number of the
interfaces didn't work properly, so I just used IDispatch (and Excel's VBA
reference) to get things out of Excel as early as possible (or into it as
late as possible). That might not be the best approach nowadays, but the
performance impact is minimal because the Excel interaction is minimal.
I share your disdain for the design of Office's COM interfaces, but I do
appreciate their availability for me to abuse as I see fit.
I wonder whether a combined/reused hierarchy would be worth creating and
maintaining. I know that with Outlook, I'm always annoyed at how shallow
the default hierarchy is (almost everything's a direct subclass of
IDispatch), but it's never seemed worth refactoring it, since I really want
to be able to regenerate it with as little subsequent rework as possible.
(Come to think of it, though, I haven't had to regenerate it for about 4
years, so maybe it wouldn't have been a problem!) Excel is the only other
MS Office application I've had to devote much time to using, and my approach
to that became increasingly minimalist with more experience.
Don
Bill Schwab
> When you say "simple wrappers around the interfaces," do you mean you
> generate them first, then tidy them up into something useful?
Please download my goodies and judge for yourself. Nothing is
generated; I like to think it is useful (at least it has been for me).
> I originally
> did that with Excel a couple of years ago, but a fair number of the
> interfaces didn't work properly,
I _think_ that's either because the IDL is trash, or because there are
many constraints on how one obtains a particular interface. I found
that the rules were not at all clear from the generated classes.
> so I just used IDispatch (and Excel's VBA
> reference) to get things out of Excel as early as possible (or into it as
> late as possible). That might not be the best approach nowadays, but the
> performance impact is minimal because the Excel interaction is minimal.
Agreed - completely.
> I share your disdain for the design of Office's COM interfaces, but I do
> appreciate their availability for me to abuse as I see fit.
Actually, I always thought it was them abusing me :) However, I share
your appreciation for them just the same.
> I wonder whether a combined/reused hierarchy would be worth creating and
> maintaining.
Take a look at my code and see what you think.
> I know that with Outlook, I'm always annoyed at how shallow
> the default hierarchy is (almost everything's a direct subclass of
> IDispatch), but it's never seemed worth refactoring it, since I really want
> to be able to regenerate it with as little subsequent rework as possible.
> (Come to think of it, though, I haven't had to regenerate it for about 4
> years, so maybe it wouldn't have been a problem!) Excel is the only other
> MS Office application I've had to devote much time to using, and my approach
> to that became increasingly minimalist with more experience.
Outlook is something I don't want to touch if I can avoid it (too risky
IMHO). Beyond that, I am proposing that we might learn a few tricks
from each other re minimalist interfacing to word and excel.