Reliable way to ensure file is finished writing to disk

Question

I regularly rely on external tools to create files from VBA: 7zip, scanning applications, etc. I need a reliable way to know when the file has finished writing and may be safely used for other operations (including passing it on to other external programs; e.g., email, etc.).

Here are the assumptions:

• I have no control over the writing of the file
• the file may or may not be on a network file server
• I don't know the contents of the file before it's been written

Because I have no control over the writing of the file, I can't use CreateFile with FILE_FLAG_WRITE_THROUGH.

Because the files may be on a network file server, I am leery of the performance impact of using FlushFileBuffers. I'm also not sure it would do what I want.

Because I don't know the contents of the file ahead of time, I can't compare hashes to check the integrity of the file.

I've used simple techniques like using Dir() to ensure that a file exists, but that seems unreliable. I've also tried pausing then retrying an operation until it works or exceeds some timeout that I have set. I've also tried opening the file with an exclusive lock and catching the error to test whether the file is still in use.

These solutions have all worked to some degree, but they all seem less than ideal.

This is a generic problem that causes frequent headaches. The result is a race condition that is difficult to reproduce and troubleshoot. Is there a better approach than what I have already tried?

Update:

As @JasonFaulkner points out in the comments, without knowing the contents ahead of time, it's impossible to be 100% sure that the file has been successfully written. Short of that, I'd like the most efficient and reliable way to determine the following conditions have been met:

• the file exists
• there is no program accessing the file
• there is no data in any cache waiting to be written to the physical disk

Answer 1

I've come up with the following procedure to check if a file is ready. I'm open to any suggestions for improvements or problems that I may have missed.

Currently this is implemented as a Sub that raises an error if the file is not ready and the retry attempts have been exhausted. If the file is ready, then the sub simply completes with no fanfare.

The following declarations go at the top of the module:

'----- VerifyFileReady declarations ----------------------------------------------------
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Declare Function CreateFile Lib "kernel32" Alias _
                                    "CreateFileA" (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, _
                                                   ByVal dwShareMode As Long, ByVal lpSecurityAttributes As Long, _
                                                   ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, _
                                                   ByVal hTemplateFile As Long) As Long
Private Const OPEN_EXISTING = 3
Private Const GENERIC_READ = &H80000000
Private Const GENERIC_WRITE = &H40000000
Private Const GENERIC_ALL = &H10000000
Private Declare Function FlushFileBuffers Lib "kernel32" (ByVal hFile As Long) As Long
Private Declare Function GetFileSize Lib "kernel32" _
                                     (ByVal hFile As Long, lpFileSizeHigh As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" _
                                 (ByVal hFile As Long) As Long
'perform 64-bit arithmetic (see: http://support.microsoft.com/kb/189862)
Private Type Curr64Bit
    Value As Currency
End Type

Private Type LongsAs64Bit
    LoValue As Long
    HiValue As Long
End Type
'=======================================================================================

Here is the sub itself, along with a small helper function to perform file size comparisons for files that may be over 2GB in size:

'---------------------------------------------------------------------------------------
' Procedure : VerifyFileReady
' Author    : Mike
' Date      : 1/22/2015
' Purpose   : Confirm that a file is ready for use; commonly used before passing a
'               filename to an outside entity for processing (e.g., a PDF printer,
'               compression utility, email, etc.)
' Parameters:
'   FName           The name of the file
'   MinSizeInBytes  The minimum file size before confirming a file is ready;
'                       by default, the file must be non-empty
'   RetryAttempts   The number of times to retry if a file is not ready
'   DelayInMs       The amount of time to sleep between retries
'   FailureMsg      Set to the reason the file is not ready; passed By Reference so that
'                       the most recent msg will be raised if necessary
'
' Notes     - Acts as a gate: if the file is ready, the program continues on; otherwise
'               an error is thrown after the number of retry ettampts is exhausted
'           - To get the maximum program delay this function will cause, multiply the
'               RetryAttempts by the DelayInMs; by default the program will delay a
'               maximum of 5 seconds (10 attempts * 500 ms delay per retry attempt)
'           - By ready for use, we mean the file meets the following criteria:
'               o the file exists
'               o the file is not locked by another process
'               o the file buffers have been flushed
'               o the file meets the minimum size in bytes (by default, it's not empty)
'           - There's no way to *really* be sure that the file has been written to disk,
'               so this function cannot guarantee transactional integrity
'---------------------------------------------------------------------------------------
'
Sub VerifyFileReady(ByVal FName As String, _
                    Optional ByVal MinSizeInBytes As Long = 1, _
                    Optional ByVal RetryAttempts As Integer = 10, _
                    Optional ByVal DelayInMs As Integer = 500, _
                    Optional ByRef FailureMsg As String = vbNullString)
    Dim FileIsReady As Boolean
    FileIsReady = True

    On Error GoTo Err_VerifyFileReady

    'FlushFileBuffers requires GENERIC_WRITE access
    Dim DesiredAccess As Long
    DesiredAccess = GENERIC_READ Or GENERIC_WRITE

    'Open the file (CreateFile is a generic function that replaces the deprecated OpenFile)
    Dim hFile As Long    'File Handle
    Err.Clear    'explicitly flush the Err.LastDllError property
    hFile = CreateFile(FName, DesiredAccess, 0, 0, OPEN_EXISTING, 0, 0)

    Dim FileOpenFailed As Boolean
    Const INVALID_HANDLE_VALUE = -1
    FileOpenFailed = (hFile = INVALID_HANDLE_VALUE)
    If FileOpenFailed Then
        FileIsReady = False
        Select Case Err.LastDLLError
        Case 2: FailureMsg = "The system cannot find the file specified."    'ERROR_FILE_NOT_FOUND
        Case 3: FailureMsg = "The system cannot find the path specified."    'ERROR_PATH_NOT_FOUND
        Case 4: FailureMsg = "The system cannot open the file."    'ERROR_TOO_MANY_OPEN_FILES
        Case 5: FailureMsg = "Access is denied."    'ERROR_ACCESS_DENIED
        Case 15: FailureMsg = "The system cannot find the drive specified."    'ERROR_INVALID_DRIVE
        Case 20: FailureMsg = "The system cannot find the device specified."    'ERROR_BAD_UNIT
        Case 21: FailureMsg = "The device is not ready."    'ERROR_NOT_READY
        Case 32: FailureMsg = "The process cannot access the file because it is being used by another process."    'ERROR_SHARING_VIOLATION
        Case 33: FailureMsg = "The process cannot access the file because another process has locked a portion of the file."    'ERROR_LOCK_VIOLATION
        Case Else: FailureMsg = "CreateFile function failed with error number " & Err.LastDLLError & "."
        End Select
    End If

    If FileIsReady Then
        'be sure the file has been physically written to disk
        Dim FlushResults As Long
        FlushResults = FlushFileBuffers(hFile)

        Dim FlushFailed As Boolean
        FlushFailed = (FlushResults = 0)
        If FlushFailed Then
            FileIsReady = False
            Select Case Err.LastDLLError
            Case 5: FailureMsg = "FlushFileBuffers function failed: Access is denied."    'ERROR_ACCESS_DENIED
            Case Else: FailureMsg = "FlushFileBuffers function failed with error number " & Err.LastDLLError & "."
            End Select
        End If
    End If

    'check that the file meets the minimum size requirement
    '   (MinSizeInBytes parameter may not exceed 2GB, but actual
    '    file sizes beyond 2GB are allowed and will be treated correctly)
    If FileIsReady And MinSizeInBytes > 0 Then
        Dim FSize64 As Curr64Bit
        Dim FileSizeLow As Long, FileSizeHigh As Long
        FileSizeLow = GetFileSize(hFile, FileSizeHigh)
        Const GetFileSizeError As Long = &HFFFFFFFF

        If FileSizeLow = GetFileSizeError Then
            FileIsReady = False
            FailureMsg = "Error getting file size."
        ElseIf TwoLongsTo64(FileSizeLow, FileSizeHigh).Value < TwoLongsTo64(MinSizeInBytes, 0).Value Then
            FileIsReady = False
            FailureMsg = "File smaller than minimum size of " & MinSizeInBytes & " byte(s)."
        End If
    End If

    'close the handle or *we* will be the ones locking the file
    If hFile <> INVALID_HANDLE_VALUE Then CloseHandle hFile

    If Not FileIsReady Then
FileNotReady:
        If RetryAttempts > 0 Then
            'we can't just raise an error or the Resume would send us back to the Err.Raise statement;
            '   instead we make a recursive call and decrement the RetryAttempts to prevent a stack overflow
            Sleep DelayInMs
            On Error GoTo 0  'prevent infinite recursion
            VerifyFileReady FName, MinSizeInBytes, RetryAttempts - 1, DelayInMs, FailureMsg
            Exit Sub
        Else
            On Error GoTo 0
            Err.Raise vbObjectError + 44212312, "FileFunctions.VerifyFileReady", FailureMsg
        End If
    End If

    Exit Sub

Err_VerifyFileReady:
    FailureMsg = "Error " & Err.Number & ": " & Err.Description
    Resume FileNotReady
End Sub

'64-bit arithmetic in VBA: http://support.microsoft.com/kb/189862
Function TwoLongsTo64(LowVal As Long, HighVal As Long) As Curr64Bit
    Dim L As LongsAs64Bit
    L.HiValue = HighVal
    L.LoValue = LowVal
    LSet TwoLongsTo64 = L
End Function