Delete / Close excel file that is kept OPEN

Hi,

We have excel reports in shared server location, which the users have access
to OPEN directly from that location. Every week these reports will be
replaced with new one. We are experiencing the "Permission denied" error when
the MS Access application tries to overwrite them with new reports. This
error occurs because some users leave the report open even after using it. We
have no idea who has kept the file OPEN. My requirement is to close or delete
the file when the file is OPEN, so that this error will not occur.

Also is there a way to identify whether a file is kept open or not, if we
know the filename?

Rgds,
Jafer

Jafer wrote:
> My requirement is to close or delete
> the file when the file is OPEN, so that this error will not occur.
>

I don't think you can do that.

> Also is there a way to identify whether a file is kept open or not, if we
> know the filename?
>
If Dir(stFileName)<>"" Then
On Error Resume Next
Kill stFileName
If Err<>0 Then
MsgBox stFileName & " is open"
Else
MsgBox stFileName & " has been deleted"
End If
On Error Goto 0
Else
MsgBox stFileName & " is missing"
End If

One wild idea: if you save the file as a template (.XLT file) then when
people open it they will get a copy and will not be holding the original
open.

Bill Manville
MVP - Microsoft Excel, Oxford, England

Hi Bill,

Thanks for the quick response. I am just thinking of saving the file as a
template (.XLT file) in the shared location. Do you think this will a good
idea?

Rgds,
Jafer



"Bill Manville" wrote:

> Jafer wrote:
> > My requirement is to close or delete
> > the file when the file is OPEN, so that this error will not occur.
> >
>
> I don't think you can do that.
>
> > Also is there a way to identify whether a file is kept open or not, if we
> > know the filename?
> >
> If Dir(stFileName)<>"" Then
> On Error Resume Next
> Kill stFileName
> If Err<>0 Then
> MsgBox stFileName & " is open"
> Else
> MsgBox stFileName & " has been deleted"
> End If
> On Error Goto 0
> Else
> MsgBox stFileName & " is missing"
> End If
>
> One wild idea: if you save the file as a template (.XLT file) then when
> people open it they will get a copy and will not be holding the original
> open.
>
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
>
>

It should overcome the problem and I haven't any better ideas

Bill Manville
MVP - Microsoft Excel, Oxford, England