Hi Dan,
Gary's resopnse is correct but to give a little more meat to bones of what
he is saying an approach along the lines of following could be used. The
FileLocked function checks to see if the file is already open read / write
mode. This is not something I have written (sorry to author for not giving
credit) but I make extensive use of it in various applications. You should
get the general idea of how to apply what Gary was saying to.
Hope helpful
Sub AddToFile()
Dim DestWB As Workbook
Dim DBFile As String
Dim mytitle As String
mytitle = "Add Data To File"
DBFile = "C:\WhichDirectory\WhichFile.xls" '<< change as required
'Check if Read / Write File Already Open
If FileLocked(DBFile) Then
msg = MsgBox("Read / Write File Already Open.", 16, mytitle)
Else
Set DestWB = Workbooks.Open(DBFile, Password:="mypassword")
'do your stuff here
DestWB.Close True
End If
End Sub
Function FileLocked(strFileName As String) As Boolean
On Error Resume Next
' If the file is already opened by another process,
' and the specified type of access is not allowed,
' the Open operation fails and an error occurs.
Open strFileName For Binary Access Read Lock Read As #1
Close #1
' If an error occurs, the document is currently open.
If Err.Number <> 0 Then
FileLocked = True
Err.Clear
End If
On Error GoTo 0
End Function
