Hello John,
Here is a macro from Microsoft that checks if a file is already open
It returns true if the file is open, false otherwise. Insert a
VB
module into your workbook and paste this code into it.
EXAMPLE
Test = IsFileOpen("C:\My Documents\MyFile.xls")
Code
-------------------
' 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.
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
-------------------
Sincerely,
Leith Ros
--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...fo&userid=1846
View this thread:
http://www.excelforum.com/showthread.php?threadid=48751