How to check if a file is opened by another user?

[gundie]

I'm using Access 2000, in VBA, how do I check whether a file (an Excel file) is opened, or in used, by another user?

 

[speakers_86]

I can't give you the answer, but I don't think there is any way to do this easily. The first idea I got was to simply try and open the file. If there is an error, then the file is already open by someone. I think that is a terrible solution for you though. If I come up with something else, I will let you know.


edit- Here is an idea. If the file is opened from within your db, then in the on open event, you can record the file being opened and by who. Inside excel, when the file is closed, you should be able to tell excel to add the close information to the table inside Access. I don't know much about communication between Access and Excel, but I think this may be doable, depending on the specifics of your requirements.

 

[ajetrumpet]

Function IsFileOpen(FileName As String)
    Dim iFilenum As Long
    Dim iErr As Long
     
    On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
    On Error GoTo 0
     
    Select Case iErr
    Case 0:    IsFileOpen = False
    Case 70:   IsFileOpen = True
    Case Else: Error iErr
    End Select
     
End Function
 
Sub mytest()
    If Not IsFileOpen("PATH") Then
        MsgBox "file is NOT open"
    Else
        MsgBox "FILE IS OPEN!"
    End If
End Sub

 

[DCrake]

Here is a specific function to check if a certain Excel file is open or not

Function IsXLBookOpen(strName As String) As Boolean
     
     'Function designed to test if a specific Excel
     'workbook is open or not.
     
    Dim i As Long, XLAppFx As Excel.Application, NotOpen As Boolean
     
     'Find/create an Excel instance
    On Error Resume Next
    Set XLAppFx = GetObject(, "Excel.Application")
    If Err.Number = 429 Then
        NotOpen = True
        Set XLAppFx = CreateObject("Excel.Application")
        Err.Clear
    End If
     
     'Loop through all open workbooks in such instance
    For i = XLAppFx.Workbooks.Count To 1 Step -1
        If XLAppFx.Workbooks(i).Name = strName Then Exit For
    Next i
     
     'Set all to False
    IsXLBookOpen = False
     
     'Perform check to see if name was found
    If i <> 0 Then IsXLBookOpen = True
     
     'Close if was closed
    If NotOpen Then XLAppFx.Quit
     
     'Release the instance
    Set XLAppFx = Nothing
     
End Function

David