Posted to microsoft.public.excel.programming
|
|
file In Use
thanks Tom - very helpful.
--
JB
"Tom Ogilvy" wrote:
Function FileLocked(strFileName As String) As Boolean
On Error Resume Next
' If the file is already opened by another process,
' and the specified type of access is not allowed,
' the Open operation fails and an error occurs.
Open strFileName For Binary Access Read Lock Read As #1
Close #1
' If an error occurs, the document is currently open.
If Err.Number < 0 Then
FileLocked = True
Err.Clear
End If
End Function
Sub OpenMyfolder()
Dim sStr as String
sStr = "P:\MyFolder\MyBook.xls"
if FileLocked(sStr) then
Workbooks.Open sStr, ReadOnly:=True
Else
Workbooks.Open sStr
End if
End Sub
--
Regards,
Tom Ogilvy
"johnboy" wrote in message
...
Steve,
thanks for response - I have tried using functions posted at various sites
to test if File is Open - but still get the message File In Use!
--
JB
"Steve Yandl" wrote:
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
|