Open of object 'IXMLHTTPRequest' fails inside loop

Question

I would like to check my server for the existence of a file every second for about ten seconds. If the file is there, download it. It it is not there (404) try again until, up to a maximum of ten times spread out over ten seconds. I don't usually code in VBA, but here goes.. I have my download function:

Function DownloadFile(url As String, fileID As String)

    ' Setup our path where we will save the downloaded file.
    Dim fileSavePath As String
    fileSavePath = Environ("USERPROFILE") & "\" & Environ("USERNAME") & "-123-" & fileID & ".xlsx"

    ' Use Microsoft.XMLHTTP in order to setup a connection.
    ' https://msdn.microsoft.com/en-us/library/ms535874(v=vs.85).aspx#methods
    Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
    ' Pass GET to the Open method in order to start the download of the file.
    WinHttpReq.Open "GET", url, False ' method, http verb, async = false

    ' Send our request: https://msdn.microsoft.com/en-us/library/ms536736(v=vs.85).aspx
    WinHttpReq.send

    ' Reset the url parameter to be the body of the response.
    url = WinHttpReq.responseBody

    ' WinHttpReq.Status holds the HTTP response code.
    If WinHttpReq.Status = 200 Then
        ' Setup an object to hold the binary stream of data (the file).
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        ' Set type read only or not: https://msdn.microsoft.com/en-us/library/ms681553(v=vs.85).aspx
        oStream.Type = 1
        ' Write the binary data to WinHttpReq.responseBody
        ' We can do this because we have confirmed a download via the response code (200).
        oStream.Write WinHttpReq.responseBody
        oStream.SaveToFile fileSavePath, 2 ' 2 = overwrites the existing file, 1 = will not.
        ' We are done we the stream, close it.
        oStream.Close
        Debug.Print "File downloaded! File path: " & fileSavePath
        DownloadFile = 1
    End If

    ' Handle if the file doesn't exist.
    If WinHttpReq.Status = 404 Then
        DownloadFile = 0
    End If

End Function

And I have a Sub which calls this function up to ten times:

Sub Callee(url As String, fileID As String)

    Dim i As Integer
    i = 0

    Do While i < 10

        If DownloadFile(url, fileID) = 1 Then
            Debug.Print "here"
            i = 100
        Else
            Debug.Print fileID & " not found! Try number: " & i
            i = i + 1
            ' We didnt get the response we wanted, so we will wait one second and try again.
            Application.Wait (Now + TimeValue("0:00:01"))
        End If

    Loop

End Sub

My code runs only once when I receive a 404 response. When the code tries to loop again I get:

I don't understand why my code runs only once, just one time through the loop. I tried to Set WinHttpReq = Nothing at the end of my function just in case some sort of garbage collection was not being taken care of, however I realize that this variable is scoped to my function, so...

Thanks for your help.

Answer 1

I'm sorry but this question and answers are misleading. The code has a bug in the line

' Reset the url parameter to be the body of the response.
url = WinHttpReq.responseBody

where url gets filled with binary data. Why are you doing this? Sure using ByVal means you get a fresh copy of url each time but why are you doing this? I commented out this line and the problem goes away.

So, IMHO, this has nothing to do with instantiation of MSXML2.XMLHTTP and garbage collection its just the url passed in was invalid.

Answer 2

Could you try to create the WinHttpReq in the Callee method and just use this object to send the request? Example:

Option Explicit

Sub Callee(url As String, fileID As String)

    ' Setup our path where we will save the downloaded file.
    Dim fileSavePath As String
    fileSavePath = Environ("USERPROFILE") & "\" & Environ("USERNAME") & "-123-" & fileID & ".xlsx"

    ' Use Microsoft.XMLHTTP in order to setup a connection.
    ' https://msdn.microsoft.com/en-us/library/ms535874(v=vs.85).aspx#methods
    Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
    ' Pass GET to the Open method in order to start the download of the file.
    WinHttpReq.Open "GET", url, False ' method, http verb, async = false

    Dim i As Integer
    i = 0

    Do While i < 10

        If DownloadFile(url, fileID, fileSavePath, WinHttpReq) = 1 Then
            Debug.Print "here"
            Exit Do
        Else
            Debug.Print fileID & " not found! Try number: " & i
            i = i + 1
            ' We didnt get the response we wanted, so we will wait one second and try again.
            Application.Wait (Now + TimeValue("0:00:01"))
        End If

    Loop

End Sub

Function DownloadFile(url As String, fileID As String, fileSavePath As String, WinHttpReq As Object)
    ' Send our request: https://msdn.microsoft.com/en-us/library/ms536736(v=vs.85).aspx
    WinHttpReq.send

    ' Reset the url parameter to be the body of the response.
    url = WinHttpReq.responseBody

    ' WinHttpReq.Status holds the HTTP response code.
    If WinHttpReq.Status = 200 Then
        ' Setup an object to hold the binary stream of data (the file).
        Dim oStream
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        ' Set type read only or not: https://msdn.microsoft.com/en-us/library/ms681553(v=vs.85).aspx
        oStream.Type = 1
        ' Write the binary data to WinHttpReq.responseBody
        ' We can do this because we have confirmed a download via the response code (200).
        oStream.Write WinHttpReq.responseBody
        oStream.SaveToFile fileSavePath, 2 ' 2 = overwrites the existing file, 1 = will not.
        ' We are done we the stream, close it.
        oStream.Close
        Debug.Print "File downloaded! File path: " & fileSavePath
        DownloadFile = 1
    End If

    ' Handle if the file doesn't exist.
    If WinHttpReq.Status = 404 Then
        DownloadFile = 0
    End If

End Function