Hi Stefan,
If you want to check that no other process has the file open, a classic
approach is to try to open it exclusively yourself. Air code:
Function IsFileAvailable(FileSpec as String) As Boolean
If Len(Dir(FileSpec)) > 0 Then 'make sure file exists
lngFileNum = FreeFile()
On Error Resume Next
Open FileSpec For Input Lock Read Write As #lngFileNum
If Err.Number = 0 Then 'file opened successfully
IsFileAvailable = True
Close #lngFileNum
Else 'file could not be opened, for whatever reason
IsFileAvailable = False
End If
On Error GoTo 0
End If
End Function
If you want to confirm that the file *is* open in an instance of Excel
running in the same Windows session, try to grab the instance of Excel
and iterate through its Workbooks collection. More air code:
Function IsWorkbookOpenLocally(FileSpec As String) As Boolean
Dim Result As Boolean
Dim oXL As Object
Dim j As Long
Result = False
On Error GoTo Err_IsWorkbookOpenLocally:
Set oXL = GetObject(, "Excel.Application")
'If we're here, Excel is running
For j = 1 to oXL.Workbooks.Count
If oXL.Workbooks(j).FullName = FileSpec Then
'Workbook is open
Result = True
Exit For
End If
Next j
Exit_IsWorkbookOpenLocally:
Set oXL = Nothing
IsWorkbookOpenLocally = Result
Exit Function
IsWorkbookOpenLocally:
Result = False
Resume Exit_IsWorkbookOpenLocally:
End Function
If you want to check multiple instances of Excel, things get more
complicated.
