Taking control of an existing instance of Excel

[nmventure]

I have two questions about gaining control of an already-running instances
of excel that was started from the Start/Programs menu, *NOT* from VBA code.

1.
Does anyone know how to locate and close a workbook named "daily.xls" that
is open in another already existing instance of Excel that was *NOT* started
from VBA code, and thus does *NOT* have an object reference to use in the
VBA code of the instance of Excel that I am working from ?

2.
Also, is it possible that after you have a way to reference the other
instance of Excel, that you can crate an object, and set it to the
already-open other instance of Excel to gain control of it for other actions
besides just closing it?

I know how to dim a new excel application and set it with CreateObject, but
that's *NOT* what I'm wanting to do here and just want to try to be clear on
what I'm looking for.

I know there must be a way to find the Windows handle and return a workbook
name but I haven't been able to figure it out yet....Thanks in
advance...Mike

[Leith Ross]

Hello Mike,

What you are looking for is contained in the Windows API. This interface can be accessed by a variety of languages. The exact coding format to access the calls depends on the langauge and object model through which communication is taking place. If you have the Windows SDK you should reference it for proper syntax and the object model you will use. You can also go to the MSDN site for more information http://www.msdn.com/.

Sincerely,
Leith Ross

[nmventure]

Thanks Leith,

what I'd like to do is find the other instance of Excel using VBA code in my
Excel application:
I open my spreadsheet "report.xls" in Excel instance "A", and maybe there
are several other instances of
Excel already running, instance "B", instance "C", and instance "D" for
example, that are already open. One of them has the "daily.xls" workbook
open in it, another has "whatever.xls" and the other has "somefile.xls"
open. I want to find the instance that has "daily.xls" open in it and gain
control of that instance. I would like to do it with vba from within my
excel program and not another program. At one time, I think I saw a reather
simple API call to do this with in vba but can't find it anywhere now that I
need it. I'll take a look at the msdn though, and thank you for the
suggestion.

Mike

"Leith Ross" <[email protected]> wrote
in message news:[email protected]...
>
> Hello Mike,
>
> What you are looking for is contained in the Windows API. This
> interface can be accessed by a variety of languages. The exact coding
> format to access the calls depends on the langauge and object model
> through which communication is taking place. If you have the Windows
> SDK you should reference it for proper syntax and the object model you
> will use. You can also go to the MSDN site for more information
> http://www.msdn.com/.
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=482138
>

[Tom Ogilvy]

http://support.microsoft.com/default...b;en-us;288902

claims you can attach to an instance of excel if you know a specific
document in that instance.

I haven't tested it, but maybe they are right. since you are looking for
Daily.xls it might work for you. Let us know.

--
Regards,
Tom Ogilvy

[Don Guillett]

You probably didn't intentionally set your clock to keep you at the top of
the list, did you?

--
Don Guillett
SalesAid Software
[email protected]
"nmventure" <[email protected]> wrote in message
news:[email protected]...
> I have two questions about gaining control of an already-running instances
> of excel that was started from the Start/Programs menu, *NOT* from VBA
code.
>
> 1.
> Does anyone know how to locate and close a workbook named "daily.xls" that
> is open in another already existing instance of Excel that was *NOT*
started
> from VBA code, and thus does *NOT* have an object reference to use in the
> VBA code of the instance of Excel that I am working from ?
>
> 2.
> Also, is it possible that after you have a way to reference the other
> instance of Excel, that you can crate an object, and set it to the
> already-open other instance of Excel to gain control of it for other
actions
> besides just closing it?
>
> I know how to dim a new excel application and set it with CreateObject,
but
> that's *NOT* what I'm wanting to do here and just want to try to be clear
on
> what I'm looking for.
>
> I know there must be a way to find the Windows handle and return a
workbook
> name but I haven't been able to figure it out yet....Thanks in
> advance...Mike
>
>

[R. Choate]

You're right, he did set his clock. What a jerk.
--
RMC,CPA


"Don Guillett" <[email protected]> wrote in message news:[email protected]...
You probably didn't intentionally set your clock to keep you at the top of
the list, did you?

--
Don Guillett
SalesAid Software
[email protected]
"nmventure" <[email protected]> wrote in message
news:[email protected]...
> I have two questions about gaining control of an already-running instances
> of excel that was started from the Start/Programs menu, *NOT* from VBA
code.
>
> 1.
> Does anyone know how to locate and close a workbook named "daily.xls" that
> is open in another already existing instance of Excel that was *NOT*
started
> from VBA code, and thus does *NOT* have an object reference to use in the
> VBA code of the instance of Excel that I am working from ?
>
> 2.
> Also, is it possible that after you have a way to reference the other
> instance of Excel, that you can crate an object, and set it to the
> already-open other instance of Excel to gain control of it for other
actions
> besides just closing it?
>
> I know how to dim a new excel application and set it with CreateObject,
but
> that's *NOT* what I'm wanting to do here and just want to try to be clear
on
> what I'm looking for.
>
> I know there must be a way to find the Windows handle and return a
workbook
> name but I haven't been able to figure it out yet....Thanks in
> advance...Mike
>
>

[nmventure]

Thanks for the suggestion Tom. The example Microsoft shows on this page is
exactly what I want to do, and I thank you, but for some reason I am
getting an "automation error" when I try to take control of the instance by
workbook name as Microsofts example shows to do.....

Set xlApp = GetObject("daily.xls").Application
or
Set xlApp = GetObject("daily").Application

both generate the message:
runtime error '-2147221020 (800401e4)'
automation error
invalid syntax

The Microsoft example, copy and pasted below directly from their page, does
not use the .xls part of the workbook or file name:
"Set xlApp = GetObject("Book2").Application"

According to them, this should work with Excel 97 through 2003. Has anyone
tried to do this with any success....
(I am using office pro 2000)

Thanks again for your assistance,

Mike




"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> http://support.microsoft.com/default...b;en-us;288902
>
> claims you can attach to an instance of excel if you know a specific
> document in that instance.
>
> I haven't tested it, but maybe they are right. since you are looking for
> Daily.xls it might work for you. Let us know.
>
> --
> Regards,
> Tom Ogilvy
>
>
>

[Peter T]

Hi Mike,

Using that method you need to include the full path, then it "usually"
works. Also to set the object directly to the application - use Parent. eg

On error resume next
set xlApp = GetObject(stringPath & "\" & "daily.xls").Parent

If not xlApp is nothing then

I use a similar method to hook into unknown instances with unknown
workbooks, but it's very convoluted!

Regards,
Peter T


"nmventure" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the suggestion Tom. The example Microsoft shows on this page
is
> exactly what I want to do, and I thank you, but for some reason I am
> getting an "automation error" when I try to take control of the instance
by
> workbook name as Microsofts example shows to do.....
>
> Set xlApp = GetObject("daily.xls").Application
> or
> Set xlApp = GetObject("daily").Application
>
> both generate the message:
> runtime error '-2147221020 (800401e4)'
> automation error
> invalid syntax
>
> The Microsoft example, copy and pasted below directly from their page,
does
> not use the .xls part of the workbook or file name:
> "Set xlApp = GetObject("Book2").Application"
>
> According to them, this should work with Excel 97 through 2003. Has
anyone
> tried to do this with any success....
> (I am using office pro 2000)
>
> Thanks again for your assistance,
>
> Mike
>
>
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > http://support.microsoft.com/default...b;en-us;288902
> >
> > claims you can attach to an instance of excel if you know a specific
> > document in that instance.
> >
> > I haven't tested it, but maybe they are right. since you are looking
for
> > Daily.xls it might work for you. Let us know.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
>
>

[nmventure]

Hi Peter,

Thanks, that's *exactly* what I'm looking for......

I had tried using the full path with the Microsoft example but got nowhere,
however, your implementation using "Parent" instead of "Application", along
with the full path, works perfectly for me, every time so far so thank you
for sharing that, it is exactly what I was looking for, and (so far) it has
worked in every test......

Thanks everyone else for their suggestions too. I'm pretty new to
newsgroups in general, and this is my first post in this one and it's
definitely a resource I'll turn to more often and contribute to as I am
able.

Mike
(using office pro 2000)


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Hi Mike,
>
> Using that method you need to include the full path, then it "usually"
> works. Also to set the object directly to the application - use Parent. eg
>
> On error resume next
> set xlApp = GetObject(stringPath & "\" & "daily.xls").Parent
>
> If not xlApp is nothing then
>
> I use a similar method to hook into unknown instances with unknown
> workbooks, but it's very convoluted!
>
> Regards,
> Peter T
>
>
> "nmventure" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the suggestion Tom. The example Microsoft shows on this page
> is
> > exactly what I want to do, and I thank you, but for some reason I am
> > getting an "automation error" when I try to take control of the instance
> by
> > workbook name as Microsofts example shows to do.....
> >
> > Set xlApp = GetObject("daily.xls").Application
> > or
> > Set xlApp = GetObject("daily").Application
> >
> > both generate the message:
> > runtime error '-2147221020 (800401e4)'
> > automation error
> > invalid syntax
> >
> > The Microsoft example, copy and pasted below directly from their page,
> does
> > not use the .xls part of the workbook or file name:
> > "Set xlApp = GetObject("Book2").Application"
> >
> > According to them, this should work with Excel 97 through 2003. Has
> anyone
> > tried to do this with any success....
> > (I am using office pro 2000)
> >
> > Thanks again for your assistance,
> >
> > Mike
> >
> >
> >
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > http://support.microsoft.com/default...b;en-us;288902
> > >
> > > claims you can attach to an instance of excel if you know a specific
> > > document in that instance.
> > >
> > > I haven't tested it, but maybe they are right. since you are looking
> for
> > > Daily.xls it might work for you. Let us know.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> >
> >
>
>

[nmventure]

I've also found this examle for visual basic that I've tried and it works
with vba in excel 2000.
http://support.microsoft.com/kb/258511/EN-US/
This one gets the windows handle which might be a more sure way of getting
the instance you want.




"nmventure" <[email protected]> wrote in message
news:[email protected]...
> I have two questions about gaining control of an already-running instances
> of excel that was started from the Start/Programs menu, *NOT* from VBA
code.
>
> 1.
> Does anyone know how to locate and close a workbook named "daily.xls" that
> is open in another already existing instance of Excel that was *NOT*
started
> from VBA code, and thus does *NOT* have an object reference to use in the
> VBA code of the instance of Excel that I am working from ?
>
> 2.
> Also, is it possible that after you have a way to reference the other
> instance of Excel, that you can crate an object, and set it to the
> already-open other instance of Excel to gain control of it for other
actions
> besides just closing it?
>
> I know how to dim a new excel application and set it with CreateObject,
but
> that's *NOT* what I'm wanting to do here and just want to try to be clear
on
> what I'm looking for.
>
> I know there must be a way to find the Windows handle and return a
workbook
> name but I haven't been able to figure it out yet....Thanks in
> advance...Mike
>
>

[Peter T]

Hi Mike,

Getting an application's window handle does not directly help attach a
reference to the application. With the MS example you don't know which app
instance the handle relates to, but even if you do it doesn't help in
setting a ref to the app (AFAIK). Having said that getting "all" app' window
handles is one of many steps in the process I use to grab all unknown
running instances, it's not straightforward.

For your particular purposes think I would stick with the GetObject method
that works for you. But I'll stand corrected.

Regards,
Peter T

"nmventure" <[email protected]> wrote in message
news:[email protected]...
> I've also found this examle for visual basic that I've tried and it works
> with vba in excel 2000.
> http://support.microsoft.com/kb/258511/EN-US/
> This one gets the windows handle which might be a more sure way of getting
> the instance you want.
>
>
>
>
> "nmventure" <[email protected]> wrote in message
> news:[email protected]...
> > I have two questions about gaining control of an already-running
instances
> > of excel that was started from the Start/Programs menu, *NOT* from VBA
> code.
> >
> > 1.
> > Does anyone know how to locate and close a workbook named "daily.xls"
that
> > is open in another already existing instance of Excel that was *NOT*
> started
> > from VBA code, and thus does *NOT* have an object reference to use in
the
> > VBA code of the instance of Excel that I am working from ?
> >
> > 2.
> > Also, is it possible that after you have a way to reference the other
> > instance of Excel, that you can crate an object, and set it to the
> > already-open other instance of Excel to gain control of it for other
> actions
> > besides just closing it?
> >
> > I know how to dim a new excel application and set it with CreateObject,
> but
> > that's *NOT* what I'm wanting to do here and just want to try to be
clear
> on
> > what I'm looking for.
> >
> > I know there must be a way to find the Windows handle and return a
> workbook
> > name but I haven't been able to figure it out yet....Thanks in
> > advance...Mike
> >
> >
>
>