Search code examples
excelvbahttphttprequest

HttpRequest object set to Nothing on HTTP POST in Excel VBA

I'm doing this HTTP POST in Excel VBA and it's working fine for me, except for just one time when I got the same error my coworker is getting. He's getting the error reliably every time, running the code from the same workbook. The error always happens on the first iteration through the loop.

When the error happens, when it tries to run the Send line it goes into the VBA error handler, but there is no VBA error, and httpRequest.Status & httpRequest.responseText are not populated because httpRequest has been set to Nothing.

Differences between my setup and his is that I'm running 64-bit Windows while he's still on 32-bit Windows, and his computer was set up by the employer while I'm a contractor using my own computer which is a default retail Windows setup.

Set httpRequest = CreateObject("MSXML2.XMLHTTP")
'loop through the param strings in the array:
For iArrayPubIndex = 1 To iArrayPubCount
    sAllParams = arrayPub(iArrayPubIndex)
    httpRequest.Open "POST", sApiUrl, False
    httpRequest.setRequestHeader "Content-Type", "application/json"
    httpRequest.setRequestHeader "x-api-key", API_KEY
    httpRequest.send sAllParams
    DoEvents
Next iArrayPubIndex

Any suggestions?

Is there an alternative to MSXML2.XMLHTTP I should be using? It seems like I've seen a few different ones. This is the one I inherited on this project.

UPDATE:

We tried MSXML2.XMLHTTP.6.0 which I believe is the latest. Same result.

Solution

  • We stumbled across the anwser. It turned out that the 32-bit version of MSXML2.XMLHTTP seems to require that the params argument of the Send method (sAllParams in my example) be typed as Variant. I had typed it as String.

    We discovered this because a preliminary sample he had written was working fine for him. Plugging in my variables into his sample still worked fine for him. Trying to understand why his sample worked for him and mine did not, I realized he had not declared any of his variables; he had allowed them all to default to Variant. When I changed my declaration of sAllParams from String to Variant, it worked fine for him.

    On my computer, the 64-bit version of MSXML2.XMLHTTP was fine with sAllParams being declared as either String or Variant.