test file - is open?

[Guest]

Hi,
I made a macro which does some changes in the workbook and saves it with a
new name. But I forgot one possible error - the file with the new name still
exists, macro will rewrite it. Someone in network can have opened it at the
moment I want do rewrite.
Is there possibility of testing, if the file is able to rewrite? I don!t
want to go through Error Statement.

Thanks

karmela

 

[ward376]

This should work on a network.

Option Explicit
Dim tFile As String
Dim hFile As Long

Sub CheckOpen()
tFile = "C:\Documents and Settings\karmela\My Documents\Book1.xls"
'use the fullname (including path)

If IsFileOpen(tFile) Then
MsgBox tFile & " is open"
Else
'replace with your code
MsgBox tFile & " is not open"

End If
End Sub

Function IsFileOpen(strFullPathFileName As String) As Boolean
On Error GoTo FileOpen
hFile = FreeFile
Open strFullPathFileName For Random Access Read Write Lock Read
Write As hFile
IsFileOpen = False
Close hFile
Exit Function
FileOpen:
IsFileOpen = True
Close hFile
End Function

Cliff Edwards

 

[Guest]

Hi,

thanks... there is also On error... but maybe it is better in a separated
function as in the main procedure.

Is is possible to show, who has the file opened? You know, when openning a
file, that is opened by another user, Excel shows "this file is locked by
user xy" and you can choose - just read, get notice it is writeable... etc.

Thanks karmela

PS. Thank for existing this discussion groups, you have helped me very much

 

[ward376]

Look here:

http://www.xcelfiles.com/IsFileOpenVBA.htm

This isn't where I first found the code I use (I don't think) but it's
functionally almost identical.

You're very welcome and Thank You for the Thank You.

Cliff Edwards