Article ID: 138621
Article Last Modified on 8/17/2005
APPLIES TO
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 98 for Macintosh
- Microsoft Excel 95 Standard Edition
- Microsoft Excel 5.0 Standard Edition
This article was previously published under Q138621
For a Microsoft Excel 2002 version of this article, see 291295.
For a Microsoft Excel 2000 version of this article, see 213383.
SUMMARY
The following Microsoft Visual Basic for Applications procedure is an example of how to determine if another user has a file open. This type of macro is useful if you are using a file that is shared over a network where only one user at a time should open the file.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
Sample Visual Basic Procedures
This example checks to see if Book2.xls in the root directory of drive C is already open. If the file is not already open, the macro will open it.
Sub TestFileOpened()
' Test to see if the file is open.
If IsFileOpen("c:\Book2.xls") Then
' Display a message stating the file in use.
MsgBox "File already in use!"
'
' Add code here to handle case where file is open by another
' user.
'
Else
' Display a message stating the file is not in use.
MsgBox "File not in use!"
' Open the file in Microsoft Excel.
Workbooks.Open "c:\Book2.xls"
'
' Add code here to handle case where file is NOT open by
' another user.
'
End If
End Sub
' 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(filename As String)
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 filename 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
Additional query words: 5.00a 5.00c 8.00 XL98 XL97 XL7 XL5 shared share vbapp locked XL
Keywords: kbhowto kbprogramming kbdtacode KB138621
