469,963 Members | 2,227 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,963 developers. It's quick & easy.

how to check if an excel file stored on a network drive is in use?

I have an excel file that I need to check if its in use by another before i open it.
I have found various forms of the follwing code but each one give me the same errors at the following 2 lines when I compile it using vb.net 2005 express
Open strFileSpec For Input Lock Read As intFn
'error = open not declared, missing comma etc.
'error = to many arguments to public Sub Close()
I'd really appreciate any ideas!

Function IsFileOpen(ByVal strFileSpec As String) As Boolean
' Purpose Test to see if the file has been locked by another process
Dim intFn As Integer, lngErrNum As Long, strErrDescr As String

On Error Resume Next ' No Error checking
intFn = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open strFileSpec For Input Lock Read As intFn
Close(intFn) ' Close the file.
lngErrNum = Err.Number ' Save the error number that occurred.
strErrDescr = Err.Description
On Error GoTo 0 ' Turn error checking back on.
' Report the error
Select Case lngErrNum
Case 0
' No error occurred - File is NOT already open by another user.
IsFileOpen = False
Case 70
' Error number for "Permission Denied. - File is opened by another user.
IsFileOpen = True
Case Else
' Yikes some other error occurred.
IsFileOpen = False
MsgBox("Error! " & vbCrLf & "Error Number " & lngErrNum & vbCrLf & "Error Descr " & strErrDescr, vbExclamation, "IsFileOpen()")
End Select
End Function
Oct 16 '07 #1
0 1774

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Rich Ulichny | last post: by
6 posts views Thread by Daniel | last post: by
5 posts views Thread by =?Utf-8?B?U3R1YXJ0?= | last post: by
9 posts views Thread by Doug Glancy | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.