Determine if an excel file is already open

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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....
 
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)
 
Back
Top