OLE Automation with Excel
Ruben Ramirez
> I dont know what part of this is due to OLE automation, its support
> in Delphi or peculiarities of Excel, so if anyone has ideas or
> suggestions, I'd be grateful.
>
> Best Regards
> --
> ---------------------------------------
> Yiannis Antoniou
> yia...@cs.ntua.gr
What you have to do is look at the documentation that comes with Excel and find
out what are methods it exposes. For example, for Microsft Word you look at the
file Worbasic.hlp to find out what commands you can issue through OLE automation.
It's not the job of Delphi to give all the interfaces that are available by every
application that you can use through OLE.
Hope this helps,
Eugene Ruben Ramirez
email: rram...@ix.netcom.com
Yiannis Antoniou
I am interested in any info regarding the use of OLE Automation
in Delphi 2.0 . The demo programs provided with Delphi help
at first, but I seem to have come upon major stumbling blocks,
and the online help is skimpy on the subject. Specifically, I want
to communicate with Microsoft Excel. Executing the following
code:
procedure .....
var v:variant;
begin
V := CreateOleObject('Excel.Sheet');
V.Application.Workbooks.Open('E:\My Documents\excel\TEST.XLS');
works, i.e. if Excel wasn't already open it starts up and opens the worksheet,
but does not gain focus,
in fact it remains hidden. It is there though, because if immediately after
the above lines I add this one:
ShowMessage('testing');
then I can see by CTRL-ALT-DEL that Excel is running, even though
it is not available from the taskbar. Alternatively, if Excel was
already running, then it is available from the taskbar and the
correct file is loaded -of course focus isn't switched to it.
So my first question is, how do I ensure
that the object created by CreateOleObject gets the focus?
In the case of Excel not already running, I have found that a
line like the following:
V.Application.Dialogs(1).Show;
which displays the File Open dialog, ensures that the dialog appears, and after
it is dismissed focus is returned to Excel, which has been hidden beforehand,
including the time the dialog is displayed. If Excel is running though, then
the dialog never gets focus and the user has to manually return to
Excel to see it.
If the above line isn't included, and Excel isn't running, it seems
that after initialisation -which I observed through the
ShowMessage/CTRL ALT DEL trick described above- Excel shuts
down by itself.
Finally, how does one make the following Excel Basic code
run through OLE Automation in Delphi?
Application.Workbooks("E:\My Documents\excel\TEST.XLS").Activate
I tried the following in Delphi, but I got an error "Workbooks method
of Application class failed", so the translation is probably wrong:
V.Application.Workbooks('E:\My Documents\excel\TEST.XLS').Activate;
Daniel Polistchuck
> yia...@softlab.ece.ntua.gr (Yiannis Antoniou) wrote in article =
<4pib6m$n...@phgasos.softlab.ece.ntua.gr>...
>=20
> I am interested in any info regarding the use of OLE Automation
> in Delphi 2.0 . The demo programs provided with Delphi help
> at first, but I seem to have come upon major stumbling blocks,
> and the online help is skimpy on the subject. Specifically, I want
> to communicate with Microsoft Excel. Executing the following=20
> code:
>=20
(SNIP)
Hello!
You should try the following code:
V.AppShow; (Works with Word, may work with Excel!)
Daniel.
--=20
-------------------------------------------------------------------------=
-
Daniel Polistchuck Systems Analyst
dan...@pobox.com Technology Group
Simples Information Technology
dan...@simples.com.br
-------------------------------------------------------------------------=
-
De Busscher Rudy
yia...@softlab.ece.ntua.gr (Yiannis Antoniou) wrote:
>
>I am interested in any info regarding the use of OLE Automation
>in Delphi 2.0 . The demo programs provided with Delphi help
I also played with the OLE automation in Delphi 2.0. I used it to gain
control over Microsoft Word 7.0. But I try to answer some of your
questions .
>begin
> V := CreateOleObject('Excel.Sheet');
> V.Application.Workbooks.Open('E:\My Documents\excel\TEST.XLS');
>
>works, i.e. if Excel wasn't already open it starts up and opens the worksheet,
>but does not gain focus,
>in fact it remains hidden.
Also for Winword. There is some command AppShow that reveals your
program. Don't know to which object it belongs in EXCEL (Woinword
doesn;'t have alll thoise different objects Application, workbooks,
sheets, ...) Look it up in the help files.
>Application.Workbooks("E:\My Documents\excel\TEST.XLS").Activate
reordering of the statements maybe ??
Application.Workbooks.Activate("E:\My Documents\excel\TEST.XLS")
>
>Best Regards
>--
>---------------------------------------
>Yiannis Antoniou
>yia...@cs.ntua.gr
HTH
Yiannis Antoniou
De Busscher Rudy <Rudy.B...@agr.kuleuven.ac.be> writes:
>>begin
>> V := CreateOleObject('Excel.Sheet');
>> V.Application.Workbooks.Open('E:\My Documents\excel\TEST.XLS');
>>
>>works, i.e. if Excel wasn't already open it starts up and opens the worksheet,
>>but does not gain focus,
>>in fact it remains hidden.
>Also for Winword. There is some command AppShow that reveals your
>program. Don't know to which object it belongs in EXCEL (Woinword
Thanks for the suggestion. It's actually
v.application.visible:=true
that does the trick.
>>Application.Workbooks("E:\My Documents\excel\TEST.XLS").Activate
>reordering of the statements maybe ??
>Application.Workbooks.Activate("E:\My Documents\excel\TEST.XLS")
No, this doesn't work since Workbooks has no Activate method. What works
though is getting the path info out of the Activate method, like
this:
v.Application.Workbooks('TEST.XLS').Activate
Now if someone knew what happens at application shut down time when there
are a bunch of CreateOleObject calls... Delphi doesn't seem to do any cleanup,
will that lead to resource drain? I still think that VB4 offers better
OLE Automation support -for example, the GetObject call that can
start up an application given only a filename. It also states that setting
the variable receiving the result of this call to Nothing at application
end frees up the Ole object and the resources it consumes. The closest
thing in Delphi would have to be the assignment v:=unassigned? (v is a
variant). I've included that in my project but it doesnt seem to be
doing anything. Also, if there is any way that Delphi can use
Remote OLE Automation like VB4 does, I'd be interested in
some examples.
Steve Ginn
On 11 Jun 1996 02:30:30 +0300, yia...@softlab.ece.ntua.gr (Yiannis Antoniou) wrote:
>
>I am interested in any info regarding the use of OLE Automation
>in Delphi 2.0 . The demo programs provided with Delphi help
>at first, but I seem to have come upon major stumbling blocks,
>and the online help is skimpy on the subject. Specifically, I want
>to communicate with Microsoft Excel. Executing the following
>code:
>
I don't remember where I got this but it seems to work for me in my exercises of learning OLE Auto
with Excel:
unit Excel1;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, OleAuto,
StdCtrls;
type
TForm1 = class(TForm)
Button1: TButton;
Button2: TButton;
bFancyAry: TButton;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure bFancyAryClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.DFM}
var
V: Variant;
procedure TForm1.Button1Click(Sender: TObject);
begin
V := CreateOleObject('Excel.Sheet');
V.Range('A1:D8').Formula := 'RAND()';
V.Application.Sheets.Add;
Caption := 'Num Sheets = ' + IntToStr(V.Application.Sheets.Count);
ShowMessage('Excel Sheet Created');
end;
var
A: Variant;
procedure TForm1.Button2Click(Sender: TObject);
begin
A := CreateOleObject('Excel.Application'); //Start a new copy of Excel
A.Visible := True;
A.WorkBooks.Add;
A.Sheets.Add;
Caption := 'Num Sheets = ' + IntToStr(A.Sheets.Count);
end;
procedure TForm1.bFancyAryClick(Sender: TObject);
var
i, j: Integer;
Ch: Char;
SimpAry: Variant;
begin
SimpAry := CreateOleObject('Excel.Application'); //Start a new copy Excel
SimpAry.Visible := True;
SimpAry.WorkBooks.Add;
SimpAry.Sheets.Add;
for i := 1 to 10 do
for j := 1 to 10 do
SimpAry.Cells[j, i].Value := i * j;
for i := 1 to 10 do begin
Ch := Chr(64 + i);
SimpAry.Cells[11, i].Value := Format('=Sum(%s1:%s10)', [Ch, Ch]);
end;
end;
end.
Steve Ginn
2nd VP - Consulting Group
sg...@swfg.com
aykut uzdiyem
Yiannis Antoniou (yia...@softlab.ece.ntua.gr) wrote:
: I am interested in any info regarding the use of OLE Automation
: in Delphi 2.0 . The demo programs provided with Delphi help
: at first, but I seem to have come upon major stumbling blocks,
: and the online help is skimpy on the subject. Specifically, I want
: to communicate with Microsoft Excel. Executing the following
: code:
: procedure .....
: var v:variant;
: begin
: V := CreateOleObject('Excel.Sheet');
: V.Application.Workbooks.Open('E:\My Documents\excel\TEST.XLS');
: works, i.e. if Excel wasn't already open it starts up and opens the worksheet,
: but does not gain focus,
: in fact it remains hidden. It is there though, because if immediately after
: the above lines I add this one:
: ShowMessage('testing');
: then I can see by CTRL-ALT-DEL that Excel is running, even though
: it is not available from the taskbar. Alternatively, if Excel was
: already running, then it is available from the taskbar and the
: correct file is loaded -of course focus isn't switched to it.
: So my first question is, how do I ensure
: that the object created by CreateOleObject gets the focus?
: In the case of Excel not already running, I have found that a
: line like the following:
: V.Application.Dialogs(1).Show;
: which displays the File Open dialog, ensures that the dialog appears, and after
: it is dismissed focus is returned to Excel, which has been hidden beforehand,
: including the time the dialog is displayed. If Excel is running though, then
: the dialog never gets focus and the user has to manually return to
: Excel to see it.
: If the above line isn't included, and Excel isn't running, it seems
: that after initialisation -which I observed through the
: ShowMessage/CTRL ALT DEL trick described above- Excel shuts
: down by itself.
: Finally, how does one make the following Excel Basic code
: run through OLE Automation in Delphi?
: Application.Workbooks("E:\My Documents\excel\TEST.XLS").Activate
: I tried the following in Delphi, but I got an error "Workbooks method
: of Application class failed", so the translation is probably wrong:
: V.Application.Workbooks('E:\My Documents\excel\TEST.XLS').Activate;
: I dont know what part of this is due to OLE automation, its support
: in Delphi or peculiarities of Excel, so if anyone has ideas or
: suggestions, I'd be grateful.
: Best Regards
: --
: ---------------------------------------
: Yiannis Antoniou
: yia...@cs.ntua.gr
You should use DDE connection btw. Delphi application and EXCEL,especially
if you want to use macro commands. I think OLE Automation doesn't have anything to do with it.
I can send you part of codes if you are interested.
Good lucks,from Turkey
Aykut Uzdiyem
e73...@bach.ceng.metu.edu.tr