Test for File-Already-Open

[Guest]

I have a situation where Access runs a query and spits the results out into
an Excel file which it then opens if the user says he wants to look at it
immediately.

I'm getting an increasing number of calls where users click the appropriate
stuff and get a programmed-in generic error message. In most cases it turns
out they have run the foregoing process, looked at the file, and left it
open. Therefore, the second time they try Access tells them "Hey dummy, you
already have that file AND you have it open!"

Anybody know a way, maybe with FileSystemObject, to test not just for the
existence of a file but whether it is open? If there is one I might be able
to save my users from themselves, but I couldn't find an appropriate method
or property under FileSystemObject.

 

[Nick via AccessMonster.com]

LarryP,

I was having the same problem, and I found this procedure somewhere online:

'==============================
'Begin code

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error will occur because there is
' some other problem accessing the file.

Function IsFileOpen(strFilename As String) As Boolean
Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open strFilename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
' Error errnum
End Select
End Function

'End Code
'========================

HTH,
Nick

 

[Guest]

Thanks, Nick. Haven't had a free moment to try it yet, but it certainly
looks like it'll do what I need.