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