Macro Code to Check Whether a File Is Already Open

Article ID: 138621 - View products that this article applies to.
This article was previously published under Q138621
For a Microsoft Excel 2002 version of this article, see 291295
(http://support.microsoft.com/kb/291295/EN-US/ )
.
For a Microsoft Excel 2000 version of this article, see 213383
(http://support.microsoft.com/kb/213383/EN-US/ )
.
Expand all | Collapse all

On This Page

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.
Back to the top | Give Feedback

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
Back to the top | Give Feedback

Properties

Article ID: 138621 - Last Review: August 17, 2005 - Revision: 2.1
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 98 for Macintosh
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
Keywords: 
kbdtacode kbhowto kbprogramming KB138621
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top