Close an Excel file if is found it open.
 

I need to better understand the default when Code asks an .xlS file to
open on a shared network.

It may be my imagination, but it seems I am getting inconsistent
results whan an user has a shared network file open on his/her network
and such file is being opened on either my desktop or on an Autosys
server. I want to make changes in such file and send out e-mails based
on the results, but I do not want to save any changes in such file
(using neither the same filename nor any revised filename).

Is below correct, for the two fact situations?

Fact Situation 1 -- an user has such shared network file open, but such
file is not involved in a macro that has crashed. My macro would open
the file as 'read only' and my macro would run, and shut Excel down,
without any errors.

Fact situation 2 -- an user has such shared network file open, and such
file is involved in a macro that has crashed. My macro would not run,
unless such error is trapped.

Thanks for a response,
Chuckles123


--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=534809

Close an Excel file if is found it open.
 

The function posted earlier will not be effective for a shared file opened
over the network. For that, check out:
http://www.xcelfiles.com/IsFileOpen.html

HTH

--
Regards,
Zack Barresse, aka firefytr



"Chuckles123"
wrote in message
...

I need to better understand the default when Code asks an .xlS file to
open on a shared network.

It may be my imagination, but it seems I am getting inconsistent
results whan an user has a shared network file open on his/her network
and such file is being opened on either my desktop or on an Autosys
server. I want to make changes in such file and send out e-mails based
on the results, but I do not want to save any changes in such file
(using neither the same filename nor any revised filename).

Is below correct, for the two fact situations?

Fact Situation 1 -- an user has such shared network file open, but such
file is not involved in a macro that has crashed. My macro would open
the file as 'read only' and my macro would run, and shut Excel down,
without any errors.

Fact situation 2 -- an user has such shared network file open, and such
file is involved in a macro that has crashed. My macro would not run,
unless such error is trapped.

Thanks for a response,
Chuckles123


--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile:
http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=534809

Close an Excel file if is found it open.
 

There shouldn't be a difference in the two. If the file is open and seen
open by your station, it will prompt to open at read only. Whether or not
it errors out is highly subjective and I wouldn't feel comfortable giving a
concrete answer there. If a "macro crashed" there are many things involved
which are complex and dynamic. Best thing I can tell you is to test it all.
There is no test like actually doing it. :)

--
Regards,
Zack Barresse, aka firefytr


"Chuckles123"
wrote in message Fact Situation 1 -- an user has such shared network file
open, but such
file is not involved in a macro that has crashed. My macro would open
the file as 'read only' and my macro would run, and shut Excel down,
without any errors.

Fact situation 2 -- an user has such shared network file open, and such
file is involved in a macro that has crashed. My macro would not run,
unless such error is trapped.

Close an Excel file if is found it open.
 

I have thoroughly tested Fact Situation 1, with each of the followin
statements, separately, in my code:

Application.DisplayAlerts = False
'Application.DisplayAlerts = False

In both cases, the file to be opened by the macro was, firstly, opene
by another user on our network; in both cases, the file was opened b
the macro as 'Read Only' with no pop-ups.

I will also test Fact Situation 2; I guess it is not important for th
macro to "crash" on the other user's machine - if the other user'
macro is being "stepped thru" and such code opens the file of interes
and such user "does nuthin" to allow for my testing <-- that shoul
work as well. Do you agree?

Thanks for a response,
Chuckles12

--
Chuckles12
-----------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...fo&userid=1494
View this thread: http://www.excelforum.com/showthread.php?threadid=53480

Close an Excel file if is found it open.
 

2 FACT SITUATIONS IN MY POST OF APR 27

VBA works differently than Excel 2003 in this regard: VBA _makes_ the
assumption that you want to open a file as 'Read Only' if that file
should be currently open on another user's machine <-- this is true
even if VBA has already processed an "Application.DisplayAlerts = True"
(this is the default) statement. The above also applies if another user
is running a macro that has opened that same file.

Chuckles123


--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=534809