Follow on to: Determine if a workbook is already open
 

Follow-up question to the post "Determine if a workbook is already open":

Given:
- At least two excel instances running. Only two are of interest, call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I can
manipulate (read, write) its contents from the VBA code in Excel instance A?

Thanks for any thoughts...

Charles

Follow on to: Determine if a workbook is already open
 

The quick answer is that you don't. One instance of Excel has no knowledge of
any other instances of Excel. Assuming that you can get a handle to a second
instance (which undoubtedly is possible) how would you know that you had the
correct instance. There may be 3 or more instances running. By far your best
solution will be to figure out how to keep everything in one instance. Just
my two cents but I see a world of difficutly with multiple instances.

If you intend to continue in this line though I would look at enumerating
32bit applications to get handles to the instances of Excel...
--
HTH...

Jim Thomlinson


" wrote:

Follow-up question to the post "Determine if a workbook is already open":

Given:
- At least two excel instances running. Only two are of interest, call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I can
manipulate (read, write) its contents from the VBA code in Excel instance A?

Thanks for any thoughts...

Charles

Follow on to: Determine if a workbook is already open
 

I'd love to limit my scope to the one instance of Excel but unfortunately
that's not in the cards for this task :(.

To respond to your question...
If I can find a way to enumerate all the instances of Excel that are running
then in each instance I can search for my TargetWkbk filename (which is known
in given in Instance A).

Looks like I'm stuck with "enumerating 32bit applications to get handles to
the instances of Excel". I'm a newbie to that sort of thing... any other
information along this vien would be greatly appreciated.

Thanks for the help
-Charles

"Jim Thomlinson" wrote:

The quick answer is that you don't. One instance of Excel has no knowledge of
any other instances of Excel. Assuming that you can get a handle to a second
instance (which undoubtedly is possible) how would you know that you had the
correct instance. There may be 3 or more instances running. By far your best
solution will be to figure out how to keep everything in one instance. Just
my two cents but I see a world of difficutly with multiple instances.

If you intend to continue in this line though I would look at enumerating
32bit applications to get handles to the instances of Excel...
--
HTH...

Jim Thomlinson


" wrote:

Follow-up question to the post "Determine if a workbook is already open":

Given:
- At least two excel instances running. Only two are of interest, call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I can
manipulate (read, write) its contents from the VBA code in Excel instance A?

Thanks for any thoughts...

Charles

Follow on to: Determine if a workbook is already open
 

If the file is open in other instances then it will open read only in this
instance. Why not open the file and check if it is readonly. If it is just
prompt the user to close it something like this...

Workbooks.Open "C:\This.xls"
Set wbkOpened = Workbooks("This.xls")
If wbkOpened.ReadOnly = True Then
MsgBox "Read Only. Please close other instances."
wbkOpened.Close
End If
--
HTH...

Jim Thomlinson


" wrote:

I'd love to limit my scope to the one instance of Excel but unfortunately
that's not in the cards for this task :(.

To respond to your question...
If I can find a way to enumerate all the instances of Excel that are running
then in each instance I can search for my TargetWkbk filename (which is known
in given in Instance A).

Looks like I'm stuck with "enumerating 32bit applications to get handles to
the instances of Excel". I'm a newbie to that sort of thing... any other
information along this vien would be greatly appreciated.

Thanks for the help
-Charles

"Jim Thomlinson" wrote:

The quick answer is that you don't. One instance of Excel has no knowledge of
any other instances of Excel. Assuming that you can get a handle to a second
instance (which undoubtedly is possible) how would you know that you had the
correct instance. There may be 3 or more instances running. By far your best
solution will be to figure out how to keep everything in one instance. Just
my two cents but I see a world of difficutly with multiple instances.

If you intend to continue in this line though I would look at enumerating
32bit applications to get handles to the instances of Excel...
--
HTH...

Jim Thomlinson


" wrote:

Follow-up question to the post "Determine if a workbook is already open":

Given:
- At least two excel instances running. Only two are of interest, call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I can
manipulate (read, write) its contents from the VBA code in Excel instance A?

Thanks for any thoughts...

Charles

Follow on to: Determine if a workbook is already open
 

Problem is that this VBA code will run unattended, scheduled for the middle
of the night... I've got no user to help me out... :(

But read only my be good enough for me at the moment...

SO you are saying that if I just set a workbook reference to the file I'm
interested in I'll get a read only version of it? Can you confirm?

Thanks again for your suggestions.

"Jim Thomlinson" wrote:

If the file is open in other instances then it will open read only in this
instance. Why not open the file and check if it is readonly. If it is just
prompt the user to close it something like this...

Workbooks.Open "C:\This.xls"
Set wbkOpened = Workbooks("This.xls")
If wbkOpened.ReadOnly = True Then
MsgBox "Read Only. Please close other instances."
wbkOpened.Close
End If
--
HTH...

Jim Thomlinson


" wrote:

I'd love to limit my scope to the one instance of Excel but unfortunately
that's not in the cards for this task :(.

To respond to your question...
If I can find a way to enumerate all the instances of Excel that are running
then in each instance I can search for my TargetWkbk filename (which is known
in given in Instance A).

Looks like I'm stuck with "enumerating 32bit applications to get handles to
the instances of Excel". I'm a newbie to that sort of thing... any other
information along this vien would be greatly appreciated.

Thanks for the help
-Charles

"Jim Thomlinson" wrote:

The quick answer is that you don't. One instance of Excel has no knowledge of
any other instances of Excel. Assuming that you can get a handle to a second
instance (which undoubtedly is possible) how would you know that you had the
correct instance. There may be 3 or more instances running. By far your best
solution will be to figure out how to keep everything in one instance. Just
my two cents but I see a world of difficutly with multiple instances.

If you intend to continue in this line though I would look at enumerating
32bit applications to get handles to the instances of Excel...
--
HTH...

Jim Thomlinson


" wrote:

Follow-up question to the post "Determine if a workbook is already open":

Given:
- At least two excel instances running. Only two are of interest, call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I can
manipulate (read, write) its contents from the VBA code in Excel instance A?

Thanks for any thoughts...

Charles

Follow on to: Determine if a workbook is already open
 

If someone else (or another instance of excel) has a spreadsheet open then
you normally get that message if you try to open it that it is "locked for
editing would you like to open a read only copy". The code that I gave you
opens the file regardless. If it is open somewhere else then it is opened
read only. Now you know and can react to the situation. Even if you check
multiple instances you still can not be 100% sure that it is not open on a
different machine. If the code is intended to run overnight then use a simple
validation routine that you run before you leave to ensure that there is
un-encumbered access to all of the necessary files. This is simple to
implement. Relatively fool proof. Not a lot of work to administer.
--
HTH...

Jim Thomlinson


" wrote:

Problem is that this VBA code will run unattended, scheduled for the middle
of the night... I've got no user to help me out... :(

But read only my be good enough for me at the moment...

SO you are saying that if I just set a workbook reference to the file I'm
interested in I'll get a read only version of it? Can you confirm?

Thanks again for your suggestions.

"Jim Thomlinson" wrote:

If the file is open in other instances then it will open read only in this
instance. Why not open the file and check if it is readonly. If it is just
prompt the user to close it something like this...

Workbooks.Open "C:\This.xls"
Set wbkOpened = Workbooks("This.xls")
If wbkOpened.ReadOnly = True Then
MsgBox "Read Only. Please close other instances."
wbkOpened.Close
End If
--
HTH...

Jim Thomlinson


" wrote:

I'd love to limit my scope to the one instance of Excel but unfortunately
that's not in the cards for this task :(.

To respond to your question...
If I can find a way to enumerate all the instances of Excel that are running
then in each instance I can search for my TargetWkbk filename (which is known
in given in Instance A).

Looks like I'm stuck with "enumerating 32bit applications to get handles to
the instances of Excel". I'm a newbie to that sort of thing... any other
information along this vien would be greatly appreciated.

Thanks for the help
-Charles

"Jim Thomlinson" wrote:

The quick answer is that you don't. One instance of Excel has no knowledge of
any other instances of Excel. Assuming that you can get a handle to a second
instance (which undoubtedly is possible) how would you know that you had the
correct instance. There may be 3 or more instances running. By far your best
solution will be to figure out how to keep everything in one instance. Just
my two cents but I see a world of difficutly with multiple instances.

If you intend to continue in this line though I would look at enumerating
32bit applications to get handles to the instances of Excel...
--
HTH...

Jim Thomlinson


" wrote:

Follow-up question to the post "Determine if a workbook is already open":

Given:
- At least two excel instances running. Only two are of interest, call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I can
manipulate (read, write) its contents from the VBA code in Excel instance A?

Thanks for any thoughts...

Charles

Follow on to: Determine if a workbook is already open
 

Great thanks for the help

"Jim Thomlinson" wrote:

If someone else (or another instance of excel) has a spreadsheet open then
you normally get that message if you try to open it that it is "locked for
editing would you like to open a read only copy". The code that I gave you
opens the file regardless. If it is open somewhere else then it is opened
read only. Now you know and can react to the situation. Even if you check
multiple instances you still can not be 100% sure that it is not open on a
different machine. If the code is intended to run overnight then use a simple
validation routine that you run before you leave to ensure that there is
un-encumbered access to all of the necessary files. This is simple to
implement. Relatively fool proof. Not a lot of work to administer.
--
HTH...

Jim Thomlinson


" wrote:

Problem is that this VBA code will run unattended, scheduled for the middle
of the night... I've got no user to help me out... :(

But read only my be good enough for me at the moment...

SO you are saying that if I just set a workbook reference to the file I'm
interested in I'll get a read only version of it? Can you confirm?

Thanks again for your suggestions.

"Jim Thomlinson" wrote:

If the file is open in other instances then it will open read only in this
instance. Why not open the file and check if it is readonly. If it is just
prompt the user to close it something like this...

Workbooks.Open "C:\This.xls"
Set wbkOpened = Workbooks("This.xls")
If wbkOpened.ReadOnly = True Then
MsgBox "Read Only. Please close other instances."
wbkOpened.Close
End If
--
HTH...

Jim Thomlinson


" wrote:

I'd love to limit my scope to the one instance of Excel but unfortunately
that's not in the cards for this task :(.

To respond to your question...
If I can find a way to enumerate all the instances of Excel that are running
then in each instance I can search for my TargetWkbk filename (which is known
in given in Instance A).

Looks like I'm stuck with "enumerating 32bit applications to get handles to
the instances of Excel". I'm a newbie to that sort of thing... any other
information along this vien would be greatly appreciated.

Thanks for the help
-Charles

"Jim Thomlinson" wrote:

The quick answer is that you don't. One instance of Excel has no knowledge of
any other instances of Excel. Assuming that you can get a handle to a second
instance (which undoubtedly is possible) how would you know that you had the
correct instance. There may be 3 or more instances running. By far your best
solution will be to figure out how to keep everything in one instance. Just
my two cents but I see a world of difficutly with multiple instances.

If you intend to continue in this line though I would look at enumerating
32bit applications to get handles to the instances of Excel...
--
HTH...

Jim Thomlinson


" wrote:

Follow-up question to the post "Determine if a workbook is already open":

Given:
- At least two excel instances running. Only two are of interest, call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I can
manipulate (read, write) its contents from the VBA code in Excel instance A?

Thanks for any thoughts...

Charles

Follow on to: Determine if a workbook is already open
 

Can you put code in a governor workbook in the "remote" instance of Excel
that checks ANOTHER file periodically (maybe even quite frequently), like a
text file, and if it sees a value it will interpret as "Get out of the
needed workbook" it will close it, then your second instance can open it, do
what is necessary, close it, and write "I am done with it" back to the text
file and then The governor workbook can check that text file again, see
there is no need of the workbook, open it again and resume where it left
off?


" wrote in
message ...
Problem is that this VBA code will run unattended, scheduled for the
middle
of the night... I've got no user to help me out... :(

But read only my be good enough for me at the moment...

SO you are saying that if I just set a workbook reference to the file I'm
interested in I'll get a read only version of it? Can you confirm?

Thanks again for your suggestions.

"Jim Thomlinson" wrote:

If the file is open in other instances then it will open read only in
this
instance. Why not open the file and check if it is readonly. If it is
just
prompt the user to close it something like this...

Workbooks.Open "C:\This.xls"
Set wbkOpened = Workbooks("This.xls")
If wbkOpened.ReadOnly = True Then
MsgBox "Read Only. Please close other instances."
wbkOpened.Close
End If
--
HTH...

Jim Thomlinson


" wrote:

I'd love to limit my scope to the one instance of Excel but
unfortunately
that's not in the cards for this task :(.

To respond to your question...
If I can find a way to enumerate all the instances of Excel that are
running
then in each instance I can search for my TargetWkbk filename (which is
known
in given in Instance A).

Looks like I'm stuck with "enumerating 32bit applications to get
handles to
the instances of Excel". I'm a newbie to that sort of thing... any
other
information along this vien would be greatly appreciated.

Thanks for the help
-Charles

"Jim Thomlinson" wrote:

The quick answer is that you don't. One instance of Excel has no
knowledge of
any other instances of Excel. Assuming that you can get a handle to a
second
instance (which undoubtedly is possible) how would you know that you
had the
correct instance. There may be 3 or more instances running. By far
your best
solution will be to figure out how to keep everything in one
instance. Just
my two cents but I see a world of difficutly with multiple instances.

If you intend to continue in this line though I would look at
enumerating
32bit applications to get handles to the instances of Excel...
--
HTH...

Jim Thomlinson


" wrote:

Follow-up question to the post "Determine if a workbook is already
open":

Given:
- At least two excel instances running. Only two are of interest,
call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows
that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the
previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I
can
manipulate (read, write) its contents from the VBA code in Excel
instance A?

Thanks for any thoughts...

Charles

Follow on to: Determine if a workbook is already open
 

Good suggestion, but that won't work for me in this instance. I've got no
control over what other processes (Excel or otherwise) are running on this
machine...

Thanks for the input though.

"William Benson" wrote:

Can you put code in a governor workbook in the "remote" instance of Excel
that checks ANOTHER file periodically (maybe even quite frequently), like a
text file, and if it sees a value it will interpret as "Get out of the
needed workbook" it will close it, then your second instance can open it, do
what is necessary, close it, and write "I am done with it" back to the text
file and then The governor workbook can check that text file again, see
there is no need of the workbook, open it again and resume where it left
off?


" wrote in
message ...
Problem is that this VBA code will run unattended, scheduled for the
middle
of the night... I've got no user to help me out... :(

But read only my be good enough for me at the moment...

SO you are saying that if I just set a workbook reference to the file I'm
interested in I'll get a read only version of it? Can you confirm?

Thanks again for your suggestions.

"Jim Thomlinson" wrote:

If the file is open in other instances then it will open read only in
this
instance. Why not open the file and check if it is readonly. If it is
just
prompt the user to close it something like this...

Workbooks.Open "C:\This.xls"
Set wbkOpened = Workbooks("This.xls")
If wbkOpened.ReadOnly = True Then
MsgBox "Read Only. Please close other instances."
wbkOpened.Close
End If
--
HTH...

Jim Thomlinson


" wrote:

I'd love to limit my scope to the one instance of Excel but
unfortunately
that's not in the cards for this task :(.

To respond to your question...
If I can find a way to enumerate all the instances of Excel that are
running
then in each instance I can search for my TargetWkbk filename (which is
known
in given in Instance A).

Looks like I'm stuck with "enumerating 32bit applications to get
handles to
the instances of Excel". I'm a newbie to that sort of thing... any
other
information along this vien would be greatly appreciated.

Thanks for the help
-Charles

"Jim Thomlinson" wrote:

The quick answer is that you don't. One instance of Excel has no
knowledge of
any other instances of Excel. Assuming that you can get a handle to a
second
instance (which undoubtedly is possible) how would you know that you
had the
correct instance. There may be 3 or more instances running. By far
your best
solution will be to figure out how to keep everything in one
instance. Just
my two cents but I see a world of difficutly with multiple instances.

If you intend to continue in this line though I would look at
enumerating
32bit applications to get handles to the instances of Excel...
--
HTH...

Jim Thomlinson


" wrote:

Follow-up question to the post "Determine if a workbook is already
open":

Given:
- At least two excel instances running. Only two are of interest,
call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows
that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the
previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I
can
manipulate (read, write) its contents from the VBA code in Excel
instance A?

Thanks for any thoughts...

Charles

Follow on to: Determine if a workbook is already open
 

I thought this would work for me, but the code you cite below has a problem.
If the Excel workbook is already open and I run the code below, I get
prompted with a message stating: "<workbook name is already open. Reopening
will cause any changes you made to be discarded. Do you want to reopen
<workbook name?" with the buttons, Yes an No.

Is there a way to supress these messages and always answer yes to the above
prompt?

"Jim Thomlinson" wrote:

If someone else (or another instance of excel) has a spreadsheet open then
you normally get that message if you try to open it that it is "locked for
editing would you like to open a read only copy". The code that I gave you
opens the file regardless. If it is open somewhere else then it is opened
read only. Now you know and can react to the situation. Even if you check
multiple instances you still can not be 100% sure that it is not open on a
different machine. If the code is intended to run overnight then use a simple
validation routine that you run before you leave to ensure that there is
un-encumbered access to all of the necessary files. This is simple to
implement. Relatively fool proof. Not a lot of work to administer.
--
HTH...

Jim Thomlinson


" wrote:

Problem is that this VBA code will run unattended, scheduled for the middle
of the night... I've got no user to help me out... :(

But read only my be good enough for me at the moment...

SO you are saying that if I just set a workbook reference to the file I'm
interested in I'll get a read only version of it? Can you confirm?

Thanks again for your suggestions.

"Jim Thomlinson" wrote:

If the file is open in other instances then it will open read only in this
instance. Why not open the file and check if it is readonly. If it is just
prompt the user to close it something like this...

Workbooks.Open "C:\This.xls"
Set wbkOpened = Workbooks("This.xls")
If wbkOpened.ReadOnly = True Then
MsgBox "Read Only. Please close other instances."
wbkOpened.Close
End If
--
HTH...

Jim Thomlinson


" wrote:

I'd love to limit my scope to the one instance of Excel but unfortunately
that's not in the cards for this task :(.

To respond to your question...
If I can find a way to enumerate all the instances of Excel that are running
then in each instance I can search for my TargetWkbk filename (which is known
in given in Instance A).

Looks like I'm stuck with "enumerating 32bit applications to get handles to
the instances of Excel". I'm a newbie to that sort of thing... any other
information along this vien would be greatly appreciated.

Thanks for the help
-Charles

"Jim Thomlinson" wrote:

The quick answer is that you don't. One instance of Excel has no knowledge of
any other instances of Excel. Assuming that you can get a handle to a second
instance (which undoubtedly is possible) how would you know that you had the
correct instance. There may be 3 or more instances running. By far your best
solution will be to figure out how to keep everything in one instance. Just
my two cents but I see a world of difficutly with multiple instances.

If you intend to continue in this line though I would look at enumerating
32bit applications to get handles to the instances of Excel...
--
HTH...

Jim Thomlinson


" wrote:

Follow-up question to the post "Determine if a workbook is already open":

Given:
- At least two excel instances running. Only two are of interest, call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I can
manipulate (read, write) its contents from the VBA code in Excel instance A?

Thanks for any thoughts...

Charles

Follow on to: Determine if a workbook is already open
 

" wrote:

Follow-up question to the post "Determine if a workbook is already open":

Given:
- At least two excel instances running. Only two are of interest, call them
A and B
- Excel instance B has a workbook open that I want to access for
manipulation, call it TargetWkbk
- Excel instance A has VBA code that I am executing... it knows that the
TargetWkbk is not open in this Excel instance (i.e. A) but that the
TargetWkbk is open somewhere (this is what I learned todo from the previous
post)

My Question:
How can I get a reference (handle?) to Excel instance B so that I can
manipulate (read, write) its contents from the VBA code in Excel instance A?

Thanks for any thoughts...

Charles