ADODB.Stream.saveToFile -> Wait until file is saved?

Question

I am wondering about the behaviour of ostream.saveToFile. I download a vba module from a server and then add it to the current workbook onWorkbookOpen. But sometimes the module is not added to the workbook onWorkbookOpen.

I guess the reason could be, that the file is not available for import.

Does the code after stream.saveToFile run asynchronously or does it wait, until the file is saved to the destination? Is there a callback to check, if the file is actually saved?

Code Snippet:

 Dim WinHttpReq As Object
 Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
 WinHttpReq.Option(4) = 13056 ' Ignore SSL Errors

 WinHttpReq.Open "GET", myURL, False     

 WinHttpReq.setRequestHeader "Accept", "*/*"
 WinHttpReq.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
 WinHttpReq.setRequestHeader "Proxy-Connection", "Keep-Alive"
 WinHttpReq.Send

 myURL = WinHttpReq.ResponseBody
 If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
  oStream.Open
  oStream.Type = 1
  oStream.Write WinHttpReq.ResponseBody
  oStream.SaveToFile VBA.Environ("TEMP") & "\Module1.bas", 2
  oStream.Close

    If DoesComponentExist("Modul1") = True Then
    With ActiveWorkbook.VBProject
         .VBComponents.Remove .VBComponents("Modul1")
    End With
    End If
    If DoesComponentExist("Modul1") = False Then
         ThisWorkbook.VBProject.VBComponents.Import (VBA.Environ("TEMP") & "\Module1.bas")
    End If

  Else
        MsgBox "Returncode:" & WinHttpReq.Status & " Unable to download Code."
 End If

Answer 1

The ADODB Stream SaveToFile method is documented here by MSDN - no mention of whether it's synchronous or asynchronous, so I'm guessing that it is actually synchronous, and returns when the process is complete and the file handle is released...

...Except that I've learned not to trust that assumption about file writes: not even local writes to disk, and never over a network: modern file systems are too 'smart' and there's too much cacheing and intermediate logic.

The underlying issue here is that we don't have anything that can tell us that no process has the file open. Don't bother looking up API calls: all you'll get is more complicated errors, without any additional assurance. All that we can do is infer that he file's available by checking that:

1. It exists;
2. It has a nonzero length;
3. It hasn't changed since you last checked the datestamp.

My recommendation:

Check that the file's available using the VBA.FileSystem functions.

A quick-and-dirty method is to detect a zero file size, as this is the result returned by querying the file system about a small file that's still being written:

Const MAX_WAIT As Integer = 10
sFile = VBA.Environ("TEMP") & "\Module1.bas"

' Code to check a file exists:
For i = 1 to MAX_WAIT
    If Len(VBA.FileSystem.Dir(sFile)) > 0 Then
        Exit For
        Application.Wait Now() + (i/3600/24)
    End If
Next i

If i >= MAX_WAIT Then
    ' Raise an error and exit
End If


' Code to check a file has nonzero file size:
For i = 1 to MAX_WAIT
    If VBA.FileSystem.FileLen(sFile) > 0 Then
        Exit For
        Application.Wait Now() + (i/3600/24)
    End If
Next i

If i >= MAX_WAIT Then
    ' Raise an error and exit
End If

Application.Wait is my 'pause' statement of choice in Excel - I prefer it to Sleep - but you may have your own tools: make sure you're not blocking the file write thread too!

I doubt that your bas file will be large enough to show any increase in size until completion, so the zero-or-completed test is probably good enough. For completeness, here's the code I use for larger objects:

' Code to check that file 'write' operations have stopped:
For i = 1 to MAX_WAIT
    If VBA.FileSystem.FileDateTime(sFile) > (Now() + (2.5/3600/24)) Then
        Exit For
        Application.Wait Now() + (i/3600/24)
    End If
Next i

If i >= MAX_WAIT Then
    ' Raise an error.
End If

Note that I'm not using a check on file length - yes, I know you can see it increasing in File Explorer on a really long operation, and you could check against the final bit count delivered by the stream - but "It's stopped growing" And "It's as big as I expect it to get" aren't as reliable as "The file system says no-one has written to it recently" - and those assumptions about 'recently' and persistent file locks are the least stupid assumptions on the table.

Yes, 'least stupid' is as good as it gets. One day I'll write a 'Falsehoods developers believe about...' document for files: but the trouble isn't misconceptions among coders, its misleading and inconsistent behaviours from file systems.

Note, also, that I'm not using the Scripting.FileSystemObject classes for my file operations: I have no documentation that lists their File object's cacheing and buffering behaviour and I don't quite trust it to give me an immediate 'snapshot' of the file state. VBA.FileSystem is reassuringly stupid, and you can get more informative errors off of it - so much so, that you may need to insert an On Error Resume Next if the file's locked in a particularly onerous way while the write's in progress.

Postscript:

You can, of course, try a file 'move' command as a final check: but that tells you nothing that you won't discover anyway by walking face-first into the file lock when you try importing the .bas file into your project. If your problem was importing partially-written files, with no errors and warnings from the file locks, then I would actually go down the route of counting the bits and checking file sizes and moving files to a 'staging' folder. It's worth pointing out that file locking errors are a protection as well as an annoyance!