file In Use

[Guest]

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.

 

[Steve Yandl]

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

 

[Guest]

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!

 

[Tom Ogilvy]

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

 

[Guest]

thanks Tom - very helpful.
--
JB

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