cycle through Excel OLE objects
vsimi...@softwin.ro
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
@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
--
A. Sinan Unur <1u...@llenroc.ude.invalid>
(remove .invalid and reverse each component for email address)
comp.lang.perl.misc guidelines on the WWW:
http://mail.augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html
vsimi...@softwin.ro
> vsimi...@softwin.ro wrote in news:1142620048.931914.175870
> @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
> --
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
news:1142687053.6...@p10g2000cwp.googlegroups.com:
> A. Sinan Unur wrote:
>> vsimi...@softwin.ro wrote in news:1142620048.931914.175870
>> @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?
...
> 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?
vsimi...@softwin.ro
A. Sinan Unur wrote:
>
> 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
> --
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
news:1142691520.8...@u72g2000cwu.googlegroups.com:
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?
A. Sinan Unur
news:Xns978A70F8265...@127.0.0.1:
> vsimi...@softwin.ro wrote in
> news:1142691520.8...@u72g2000cwu.googlegroups.com:
>
>> A. Sinan Unur wrote:
>>>
>>> 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?
...
>> 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.
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__
vsimi...@softwin.ro
> "A. Sinan Unur" <1u...@llenroc.ude.invalid> wrote in
> news:Xns978A70F8265...@127.0.0.1:
>
> > 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.
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
news:1142700868....@e56g2000cwe.googlegroups.com:
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
vsimi...@softwin.ro
A. Sinan Unur wrote:
>
> 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
news:1142702549.7...@i40g2000cwc.googlegroups.com:
>
> A. Sinan Unur wrote:
>>
>> 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.
...
> 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.
vsimi...@softwin.ro
A. Sinan Unur wrote:
>
> 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.
>
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
@i40g2000cwc.googlegroups.com:
>
> A. Sinan Unur wrote:
>>
>> 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 ?
Yes.
> Were there more than 1 processes in the task list ?
Never. Including when I ran the script with a sleep inserted.
A. Sinan Unur
> vsimi...@softwin.ro wrote in news:1142703472.200624.191570
> @i40g2000cwc.googlegroups.com:
>
>>
>> A. Sinan Unur wrote:
>>>
>>> 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 ?
>
> Yes.
>
>> Were there more than 1 processes in the task list ?
>
> 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.
vsimi...@softwin.ro
A. Sinan Unur wrote:
> vsimi...@softwin.ro wrote in news:1142703472.200624.191570
> @i40g2000cwc.googlegroups.com:
>
> >
> > A. Sinan Unur wrote:
> >>
> >> 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 ?
>
> Yes.
>
> > Were there more than 1 processes in the task list ?
>
> Never. Including when I ran the script with a sleep inserted.
>
> Sinan
>
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
vsimi...@softwin.ro
A. Sinan Unur wrote:
> "A. Sinan Unur" <1u...@llenroc.ude.invalid> wrote in
> news:Xns978A817961BB...@127.0.0.1:
>
> > vsimi...@softwin.ro wrote in news:1142703472.200624.191570
> > @i40g2000cwc.googlegroups.com:
> >
> >>
> >> A. Sinan Unur wrote:
> >>>
> >>> 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 ?
> >
> > Yes.
> >
> >> Were there more than 1 processes in the task list ?
> >
> > 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
>
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.
A. Sinan Unur
> "A. Sinan Unur" <1u...@llenroc.ude.invalid> wrote in
> news:Xns978A82C4ED4...@127.0.0.1:
>
>> 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.
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).
vsimi...@softwin.ro wrote in news:1142705298.539381.306720
@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.
Thomas Kratz
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^.-