1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
| Option Explicit
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 occurs because there is
' some other problem accessing the file.
Private Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next
filenum = FreeFile()
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum
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
Case 0
' No error occurred.
' File is NOT already open by another user.
IsFileOpen = False
Case 70
' Error number for "Permission Denied."
' File is already opened by another user.
IsFileOpen = True
Case Else
' Another error occurred.
Error errnum
End Select
End Function |
Partager