locked
Can this test to see if a file is open fail? RRS feed

  • Question

  • Using Access 2010

    One of my MS Access applications needs to update an excel spreadsheet after the user finishes adding data in Access.

    Before I attempt to open the spreadsheet in Access for updating, I run this code (gotten I think from this forum) to see if the file is already open and thus can't be updated by my application:

    ----------------------
    'Get a free file number.
    filenum = FreeFile()
    'Attempt to open the file and lock it.
    Open ss_path_and_file For Input Lock Read As #filenum
    'Close the file.
    Close filenum
    'check for errors
    If Err <> 0 Then
       'the file is already opened do something
       MsgBox "It appears the spreadsheet is already open." & vbCrLf & "Please close the file then try 'update' again."
       UpdateSpreadsheet = 999999
       GoTo Exit_UpdateSpreadsheet
    End If
    ---------------------

    My users are complaining that they occasionally get this error when the spreadsheet is not open in some other application.
    Of course they may be wrong, so I will try to find out if they are in fact wrong. Unfortunately they are 10 miles away.

    Assuming for the moment they are not wrong, is it actually possible that this test can fail and report the file is already open when in fact it is not open?
    And if this test can fail, does anyone know what the reason(s) are?

    Thanks in advance for any help. --Fred

    Friday, December 9, 2016 2:44 PM

All replies

  • I reliably use this function to test if a doc is open.  The only reason it fails is if Word or Excel crashes and leaves a dangling file handle.  A reboot solves it.  I have another function that tells you who has file open.

    Function IsDocFileOpen2(docPath As String) As Boolean
    
      Dim fso As New Scripting.FileSystemObject
      Dim fileNum As Integer
      Dim path As String
      
      On Error GoTo ErrHandler
      path = LCase(docPath)
      IsDocFileOpen2 = False
      
      If fso.FileExists(path) Then
        fileNum = FreeFile()
        Open path For Input Lock Read As fileNum
        On Error Resume Next
        Close #fileNum
      End If
      Exit Function
    
    ErrHandler:
       IsDocFileOpen2 = True
    End Function



    • Edited by mogulman52 Friday, December 9, 2016 3:43 PM
    Friday, December 9, 2016 3:39 PM
  • Thanks for your reply mogulman52, and feel free to post the function for who has the file open.
    Friday, December 9, 2016 4:49 PM