Take a look at this function. The example is using MS Word but it should do
the trick for you.
http://word.mvps.org/FAQs/MacrosVBA/CheckIfFileOpen.htm
Steve
"johnboy" wrote in message
...
Hi all,
I am using VBA to open a workbook on a network drive with following code:
Sub OpenMyfolder()
Application.DisplayAlerts = False
Workbooks.Open("P:\MyFolder\MyBook.xls")
Application.DisplayAlerts = True
End Sub
Code works fine however, if workbook is already open on network drive, you
get a prompt to say file is in use with options to Open as Read Only, Wait
or
Cancel.
My question is, can I prevent this message showing if File is already
open?
& if it is, default to open the file as ReadOnly?
I have used Application.DisplayAlerts = False but this did not work.
Also,I
am not too sure how I should write the reuqired code to open as readonly
in
such event.
Hope clear - any help / guidance appreciated.
--
JB