cycle through Excel OLE objects

[vsimionescu]

Hello

I have a question that may sound strange but it would help a lot if I
knew the answer. Unfortunately I suspect it cannot be done. So: I'd
like to be able to loop through all Excel instances that are running,
via Win32::OLE, in order to look for a particular instance that I
created earlier. Win32::OLE->GetActiveObject, as said in the
documentation, "returns an OLE reference to a running instance of the
specified OLE automation server", it doesn't say which one. It is
implied that there is only one, or none, which may be true most of the
times but in my case there will be more, this is the whole point of it.
I couldn't find any way in the documentation (or by googling) to obtain
all Excel objects. So, please, if anybody knows of such a thing, I'd
appreaciate a hint.

Regards
V. Simionescu

 

[A. Sinan Unur]

(e-mail address removed) wrote in @j52g2000cwj.googlegroups.com:

I have a question that may sound strange but it would help a lot if I
knew the answer. Unfortunately I suspect it cannot be done. So: I'd
like to be able to loop through all Excel instances that are running,
via Win32::OLE,

Have you actually read the documentation?

http://search.cpan.org/~jdb/libwin32-0.26/OLE/lib/Win32/OLE.pm#Methods

Have you written something that uses Win32::OLE->EnumAllObjects?

Sinan

 

[vsimionescu]

(e-mail address removed) wrote in @j52g2000cwj.googlegroups.com:


Have you actually read the documentation?

http://search.cpan.org/~jdb/libwin32-0.26/OLE/lib/Win32/OLE.pm#Methods

Have you written something that uses Win32::OLE->EnumAllObjects?

Sinan
--

I have read the documentation, I knew about this function. I think you
are missing my point. EnumAllObjects retrieves all Win32::OLE objects
currentrly existing, that is that have been created in the program
using Win32::OLE->new, Win32::OLE->GetActiveObject etc. . To be sure, I
did a test, I started an instance of Excel, didn't link to it from my
Perl program then called EnumAllObjects, and it returned 0 as expected.
I need a way to find out all Excel instances (processes) that are
running in the system, to which I haven't linked yet in Win32::OLE,
actually I need a way to link to each of them.
So you see, there's no need to be sarcastic or patronising. I know you
were trying to help but you might have shown a little more
consideration.

V. Simionescu

 

[A. Sinan Unur]

(e-mail address removed) wrote in

....

I have read the documentation, I knew about this function. I think you
are missing my point. EnumAllObjects retrieves all Win32::OLE objects
currentrly existing, that is that have been created in the program
using Win32::OLE->new, Win32::OLE->GetActiveObject etc. . To be sure,
I did a test, I started an instance of Excel, didn't link to it from
my Perl program then called EnumAllObjects, and it returned 0 as
expected. I need a way to find out all Excel instances (processes)
that are running in the system, to which I haven't linked yet in
Win32::OLE, actually I need a way to link to each of them.
So you see, there's no need to be sarcastic or patronising. I know you
were trying to help but you might have shown a little more
consideration.

You did say:

I'd like to be able to loop through all Excel instances that are
running, via Win32::OLE, in order to look for a particular instance
that I created earlier.

It is natural to assume that the instance you were looking for was
created with Win32::OLE based on that statement. If you did not, then
why not?

My response was not sarcastic or patronising but full of genuine
befuddlement.

I am still befuddled: If you yourself created the previous instance of
the Excel which you are looking for, why did you not use Win32::OLE for
it?

If there is some valid reason why, and you know what file is open in
that instance (why would you not, you created it), why can't you just
use Win32::OLE->new to get a handle to it, and interact with it?

Sinan

 

[vsimionescu]

I am still befuddled: If you yourself created the previous instance of
the Excel which you are looking for, why did you not use Win32::OLE for
it?

If there is some valid reason why, and you know what file is open in
that instance (why would you not, you created it), why can't you just
use Win32::OLE->new to get a handle to it, and interact with it?

Sinan
--

Because I created it in an earlier call of the program. My program
finishes then it is called again, it's a more complicated process. I
could let it run in the background and do some polling to retrieve
commands, but it's complicated. This is what I'll do eventually, if
there is no better way, but I was hoping maybe there is one.
So, now you know what I want, is there any such thing ?

Vlad Simionescu

 

[A. Sinan Unur]

(e-mail address removed) wrote in

Because I created it in an earlier call of the program. My program
finishes then it is called again, it's a more complicated process. I
could let it run in the background and do some polling to retrieve
commands, but it's complicated. This is what I'll do eventually, if
there is no better way, but I was hoping maybe there is one.
So, now you know what I want, is there any such thing ?

So, you launched Excel, opened or created a workbook, and did something
with it. Then, that script terminated, but the Excel workbook is still
open.

You then want to interact with the same workbook.

I think you are missing the fact that no matter how many workbooks are
open, there is still only one Excel application running on the system
when the subsequent script is started.

Presumably, there is some way of identifying the workbook you are
interested in.

My question still remains: Why not get a handle to Excel, go look at the
list of workbooks that are currently open, interact with the one you
want?

Sinan

 

[A. Sinan Unur]

(e-mail address removed) wrote in
....


So, you launched Excel, opened or created a workbook, and did
something with it. Then, that script terminated, but the Excel
workbook is still open.

You then want to interact with the same workbook.

I think you are missing the fact that no matter how many workbooks are
open, there is still only one Excel application running on the system
when the subsequent script is started.

Presumably, there is some way of identifying the workbook you are
interested in.

I would have set the name of the workbook to something meaningful.

My question still remains: Why not get a handle to Excel, go look at
the list of workbooks that are currently open, interact with the one
you want?

Basic code to do just that follows. Remember, there is only one instance
of 'Excel.Application', and there maybe more than one instance of
Win32::OLE objects during your programs lifetime, but your problem
has to do with neither of those types of instances, but just finding
a workbook among a list of workbooks.

#!/usr/bin/perl

use strict;
use warnings;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 2;

my $excel = get_office_application('Excel');
my $n_books = $excel->Workbooks->{Count};

my $lookfor = 'Book1';
my $desired_wb;

WORKBOOKS: for my $i ( 1 .. $n_books ) {
my $wb = $excel->Workbooks($i);
if ( $wb->{Name} eq $lookfor ) {
$desired_wb = $wb;
last WORKBOOKS;
}
}

if ( $desired_wb ) {
printf "Found %s\n", $desired_wb->{Name};
}

sub get_office_application {
my ($name) = @_;
my $app;
eval {
$app = Win32::OLE->GetActiveObject("$name.Application");
};

die "$@\n" if $@;

return $app if defined $app;

$app = Win32::OLE->new("$name.Application", sub { $_[0]->Quit })
or die "Oops, cannot start $name: ", Win32::OLE->LastError, "\n";
}

__END__

 

[vsimionescu]

No, that's not true, there can be more Excel instances running. At
least in Excel 97 and 2000. Try typing "Excel" in the Run item of the
Windows Start menu a few times and you'll see what I mean.

 

[A. Sinan Unur]

(e-mail address removed) wrote in

No, that's not true, there can be more Excel instances running. At
least in Excel 97 and 2000. Try typing "Excel" in the Run item of the
Windows Start menu a few times and you'll see what I mean.

I cannot test those versions because I do not have them.

Do you mean there are separate entries in the task list, or do you just
mean you get separate windows?

I strongly suspect, that the OLE server for each application is a
singleton no matter how many Excel entries you see in the task list.

You, on the other hand, could test could use the code I gave you to see
if it will find the workbook you are looking for, and report your
results.

Sinan

 

[vsimionescu]

Do you mean there are separate entries in the task list, or do you just
mean you get separate windows?

I strongly suspect, that the OLE server for each application is a
singleton no matter how many Excel entries you see in the task list.

You, on the other hand, could test could use the code I gave you to see
if it will find the workbook you are looking for, and report your
results.

Sinan

I know what your code does, I've done it myself and with no results.
Trust me, I'm not a beginner.
There are more processes in the task list. You get one OLE object for a
process, you see the documents open in that process but not those of
the other processes.
You should try what I told you in your Windows version, it probably
works the same.
Anyway, I get it you don't know how it is done. Probably there is no
way.

V. Simionescu

 

[A. Sinan Unur]

(e-mail address removed) wrote in

....

I know what your code does, I've done it myself and with no results.
Trust me, I'm not a beginner.
There are more processes in the task list. You get one OLE object for
a process, you see the documents open in that process but not those of
the other processes.
Curious.

You should try what I told you in your Windows version, it probably
works the same.

I did. No matter how many Excel windows are open, my code always finds
the one I am looking for (and, yes, I have tried it with other names
than Book1). I have Win XP SP2 with Excel 2003.

Anyway, I get it you don't know how it is done. Probably there is no
way.

There might be a Windows API way, but I don't have time for that right
now.

Sinan

 

[vsimionescu]

I did. No matter how many Excel windows are open, my code always finds
the one I am looking for (and, yes, I have tried it with other names
than Book1). I have Win XP SP2 with Excel 2003.

Did you type more than once "Excel" in Windows Start menu / Run ? Were
there more than 1 processes in the task list ?
Please, I'm curious how things are in XP / Excel 2003. I have no way to
check this right now.

 

[A. Sinan Unur]

(e-mail address removed) wrote in @i40g2000cwc.googlegroups.com:

Did you type more than once "Excel" in Windows Start menu / Run ?
Yes.

Were there more than 1 processes in the task list ?

Never. Including when I ran the script with a sleep inserted.

Sinan

 

[A. Sinan Unur]

(e-mail address removed) wrote in @i40g2000cwc.googlegroups.com:


Never. Including when I ran the script with a sleep inserted.

Arrrgh! I take that back. I had not sorted the task list by application
name. How stupid of me.

In addition, I changed my script to just list all the workbook names,
and I can see that it only returns a subset of them (all open in
separate instances started from Start -> Run).

Hmmmm ...

More later. I thought you'd want to know of my error.

Sinan

 

[vsimionescu]

(e-mail address removed) wrote in @i40g2000cwc.googlegroups.com:


Never. Including when I ran the script with a sleep inserted.

Sinan

OK, if there is only 1 Excel instance, of course you get all documents.
I suspect it has to do with Excel 2003, not with the Windows version.
They probably fixed a bug that came from behind but still existed in
Excel 2000.
Anyway, this is good info, because if this is how Excel 2003 works then
my whole approach (of having more Excel instances, this is something I
did deliberately) goes down the drain. So all this was helpful after
all. I'll check it myself but I trust that's how it is. Thanks. (BTW,
did you try to create a new Excel instance, with OLE::new, even if one
is already present ? This might still work.)
If you can get to Excel 2000 and are curious, you might test what I
told you and tell me what you think.

V. Simionescu

 

[vsimionescu]

Arrrgh! I take that back. I had not sorted the task list by application
name. How stupid of me.

In addition, I changed my script to just list all the workbook names,
and I can see that it only returns a subset of them (all open in
separate instances started from Start -> Run).

Hmmmm ...

More later. I thought you'd want to know of my error.

Sinan

OK, so you see my questions were not that pointless after all. Now you
have something to think about.
Of course, thanks for telling me about your error. I'm very interested
in how Excel 2003 works, as you know from my previous post.

Wait a minute, you might have said something very very interesting.
What if I mark my Excel instance by opening a certain document in it ?
Then I retrieve that document via OLE::GetObject ? It will probably
work. I can get the application object from the workbook object. Great,
problem solved. I'll test it later though, now I'm in the middle of
something else.

 

[A. Sinan Unur]

In addition, I changed my script to just list all the workbook names,
and I can see that it only returns a subset of them (all open in
separate instances started from Start -> Run). ....
More later. I thought you'd want to know of my error.

The Object Browser shows the names of two classes OLEObjects and
OLEObject. That line of inquiry maybe fruitful although I haven't been
able to get it to work yet.

Sinan

 

[A. Sinan Unur]

The Object Browser shows the names of two classes OLEObjects and
OLEObject. That line of inquiry maybe fruitful although I haven't been
able to get it to work yet.

Scratch that. Those are OLEObjects embedded in a given document.

Anyway, as I said before, my confusion with your approach lay in the fact
that you were trying to get a list of instances when you knew what
particular document you were interested in. It all went downhill from
there (my mental clarity, I mean).

(e-mail address removed) wrote in @v46g2000cwv.googlegroups.com:

Wait a minute, you might have said something very very interesting.
What if I mark my Excel instance by opening a certain document in it ?
Then I retrieve that document via OLE::GetObject ? It will probably
work. I can get the application object from the workbook object.

That ought to work.

Sinan

 

[Thomas Kratz]

Hello

I have a question that may sound strange but it would help a lot if I
knew the answer. Unfortunately I suspect it cannot be done. So: I'd
like to be able to loop through all Excel instances that are running,
via Win32::OLE, in order to look for a particular instance that I
created earlier. Win32::OLE->GetActiveObject, as said in the
documentation, "returns an OLE reference to a running instance of the
specified OLE automation server", it doesn't say which one. It is
implied that there is only one, or none, which may be true most of the
times but in my case there will be more, this is the whole point of it.
I couldn't find any way in the documentation (or by googling) to obtain
all Excel objects. So, please, if anybody knows of such a thing, I'd
appreaciate a hint.

http://support.microsoft.com/kb/q196776/

Look under 5. for an explanation

Thomas

--
$/=$,,$_=<DATA>,s,(.*),$1,see;__END__
s,^(.*\043),,mg,@_=map{[split'']}split;{#>J~.>_an~>>e~......>r~
$_=$_[$%][$"];y,<~>^,-++-,?{$/=--$|?'"':#..u.t.^.o.P.r.>ha~.e..
'%',s,(.),\$$/$1=1,,$;=$_}:/\w/?{y,_, ,,#..>s^~ht<._..._..c....
print}:y,.,,||last,,,,,,$_=$;;eval,redo}#.....>.e.r^.>l^..>k^.-