Kill Failed? Let User Try Again

My Access applications regularly need to overwrite existing files.  A common example is exporting data to a text file, such as a .csv.  Sometimes the user chooses to overwrite the existing file, and sometimes the program is hard-coded to save to a specific filename.

Oftentimes, the user wants to immediately re-export the data.  This fails, though, if the target file is open in a program--such as Excel--that locks the file.  The only way to overwrite the file is to close it first.

This situation comes up often enough that I wrote a function to give the user the chance to close an open file instead of raising an error.

Limitations

I should point out that this particular function is of the "quick and dirty" variety.  For most of my use cases, it is more than sufficient.  But it has some real flaws, most notably that it swallows ALL errors, not just the "file is locked" types.  

Also, since it's little more than a wrapper around the Kill statement, it's subject to all the same problems and cautions.  Namely, you need to be careful about accidentally deleting multiple files.  If you (or a user!) includes a wildcard character in the file name (* or ?), you could delete a lot more files than you intended to.  There are also Unicode considerations, which I address at the bottom of the article.

This function as an improvement over calling the Kill statement directly.  It is not the most robust possible solution for deleting files.  Code accordingly.

The Code

#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If


'https://nolongerset.com/the-subtle-dangers-of-dir/
Private Function FileExists(FullPathOfFile As String)
    Dim HasWildcards As Boolean
    HasWildcards = (InStr(FullPathOfFile, "*") > 0) Or (InStr(FullPathOfFile, "?") > 0)
    
    If HasWildcards Then
        FileExists = (Len(Dir(FullPathOfFile)) > 0)
    Else
        Dim oFSO As New Scripting.FileSystemObject
        FileExists = oFSO.FileExists(FullPathOfFile)
    End If
End Function

'---------------------------------------------------------------------------------------
' Procedure : DeleteFile
' Purpose   : Delete a file that may already be open
' Notes     - Returns True if FName does not exist
'           - Returns False if FName exists and could not be deleted
'           - Does not raise or throw errors
'---------------------------------------------------------------------------------------
'
Function DeleteFile(FName As String, _
                    Optional DelayInSeconds As Long = 0, _
                    Optional Silent As Boolean = False) As Boolean
    Dim StartTime As Date, Complete As Boolean
    On Error Resume Next
    StartTime = Now()
    Do Until Complete
        Err.Clear
        Kill FName
        If Not FileExists(FName) Then
            DeleteFile = True
            Complete = True
            Exit Function
        End If
        If Err.Number <> 0 Then
            If (Now() - StartTime) * 86400 > DelayInSeconds Then
                If Not Silent Then
                    If MsgBox("Unable to delete file:" & vbCrLf & vbCrLf & _
                              FName & vbCrLf & vbCrLf & _
                              "Ensure the file is closed and you have the permissions to delete it.", _
                              vbRetryCancel, "File Delete Failed") = vbCancel Then
                        DeleteFile = False
                        Complete = True
                        Exit Function
                    End If
                Else
                    Complete = True
                End If
            Else
                Sleep 1000 ' wait for 1 second before trying again
            End If
        End If
    Loop
End Function

Caveat Scriptor

When I posted my article on Twitter yesterday, @ExcelAnalytics replied to point out that Dir and "other legacy MS-DOS functions (Filelen, filedatetime, Open_As, Attrib, etc) do not recognise Unicode file names, which FSO does."

In light of this, you should be aware that the code above may have problems handling Unicode file names.  I don't know exactly what form those problems may take (does the Kill statement work with Unicode file names? I don't know).

I went searching for a definitive answer. I started going down the Unicode rabbit hole and had to stop before my night spiraled completely out of control.  I intend to revisit this situation at some point in the future.

Defeat of the Black Knight from "Monty Python and the Holy Grail"