XL: Macro Code to Check Whether a File Is Already Open

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

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