Determine if an excel file is already open

[Guest]

I'm having odd results using the code cited in this article,
http://support.microsoft.com?kbid=184982, to determine is an Excel file is
already open.

The problem I'm having is when an excel file is marked read-only. (Everthing
works as expected otherwise.) In my testing, when the Excel file is marked as
read only, the code below ALWAYS (wether the file is open or not) returns
Err.Number=75, Err.Description=Path/File access error.

Unfortunately, I can't assume that the file is not read-only so I need to
figure this problem out. Any idea how I can determine if a read-only file is
already open or not?

I tried removing the "Write"s from the Open statement, but that doesn't help
in this situation. What need to know is if the file is open before I attempt
to access it, so that when I'm done I can close or open it depending on the
status before I started my processing.

Thanks for any feedback...


FYI, Here's the code:

Public Sub x()
Debug.print FileLocked("C:\", "some.xls")
End sub

Function FileLocked(FilePath As String, FileName As String) As Boolean
On Error Resume Next
' Adapted from http://support.microsoft.com/?kbid=184982
' 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 FilePath & FileName For Binary Access Read Write Lock Read Write As
#1
Close #1

' If an error occurs, the document is currently open.
If Err.Number <> 0 Then
' Display the error number and description.
MsgBox "Error #" & str(Err.Number) & " - " & Err.Description
FileLocked = True
Err.Clear
End If
End Function

 

[Guest]

Nope that doesn't work. With this modification, if the file is read-only and
the Excel file is open, this this function returns False, indicating that the
file is not open....

 

[Bob Phillips]

Try this variation

Function FileLocked(FilePath As String, FileName As String) As Boolean
On Error Resume Next
' Adapted from http://support.microsoft.com/?kbid=184982
' 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 FilePath & FileName For Input Lock Read Write As #1
Close #1

' If an error occurs, the document is currently open.
If Err.Number <> 0 Then
' Display the error number and description.
MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
FileLocked = True
Err.Clear
End If
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)