Use of WinHttpRequest in Excel 2003 under XP

Question

I'm using the code below to perform POST and GET requests to my HTTP+JSON webservice. Everything runs perfectly under Win7 and Excel 2013. While testing under Windows XP and Excel 2003, the GET worked fine (request body contains JSON object), but the POST didn't. The JSON data which should be send over the line looks perfect, but it is not received on the server side, and the client code doesn't report an error. Unfortunately I can't install sniffers to see what is really send. After modifying the code to use a MSXML2.ServerXMLHTTP object, it works fine (so that will be the solution). Does anybody have experience with this problem? I'm trying to figure out why it doesn't work with WinHttpRequest...

'Copied from https://coderwall.com/p/pbxsyw/vba-web-requests
Private Function MakeWebRequest(Method As String, Url As String, PostData As String) As Boolean
    ' make sure to include the Microsoft WinHTTP Services in the project
    ' tools -> references -> Microsoft WinHTTP Services, version 5.1
    ' http://www.808.dk/?code-simplewinhttprequest
    ' http://msdn.microsoft.com/en-us/library/windows/desktop/aa384106(v=vs.85).aspx
    ' http://www.neilstuff.com/winhttp/

    On Error GoTo ErrorHandler:

    ' create the request object
    Set mobjWebReq = CreateObject("WinHttp.WinHttpRequest.5.1")

    ' set timeouts
    ' http://msdn.microsoft.com/en-us/library/windows/desktop/aa384061(v=vs.85).aspx
    ' SetTimeouts(resolveTimeout, ConnectTimeout, SendTimeout, ReceiveTimeout)
    mobjWebReq.SetTimeouts 60000, 60000, 60000, 60000

    If Not LastUsedUrlMonitor Is Nothing Then
        LastUsedUrlMonitor.Value2 = Url
    End If

    ' make the request, http verb (method), url, false to force syncronous
    ' open(http method, absolute uri to request, async (true: async, false: sync)
    mobjWebReq.Open Method, Url, False

    ' handle post content type
    If Method = "POST" Then
        mobjWebReq.SetRequestHeader "Content-type", _
          "application/json"
        If Not LastHttpBodySendMonitor Is Nothing Then
            LastHttpBodySendMonitor.Value2 = PostData
        End If
    End If

    ' set WinHttpRequestOption enumerations
    ' http://msdn.microsoft.com/en-us/library/windows/desktop/aa384108(v=vs.85).aspx

    ' set ssl ignore errors
    '   13056: ignore errors
    '   0: break on errors
    mobjWebReq.Option(4) = 13056

    ' set redirects
    mobjWebReq.Option(6) = True

    ' allow http to redirect to https
    mobjWebReq.Option(12) = True

    ' send request
    ' send post data, should be blank for a get request
    mobjWebReq.Send PostData

    MakeWebRequest = True

    If Not LastHttpBodyReceivedMonitor Is Nothing Then
        LastHttpBodyReceivedMonitor.Value2 = mobjWebReq.Status & ": " & mobjWebReq.StatusText & ", Body: " & mobjWebReq.ResponseText
    End If

    Exit Function

ErrorHandler:
    Select Case Err.Number
        Case &H80072EFD
            MsgBox "Connection to URL: " & Chr(13) & Url & Chr(13) & "failed: " & Err.Description
        Case Else
            MsgBox Err.Number & Chr(13) & Err.Description
    End Select
    Err.Clear
    'Set mobjWebReq = Nothing
    MakeWebRequest = False
    Exit Function
End Function

"but it is not received on the server side" means what? There is not even an entry in the server's access log for the POST request? But if there is one however, please have a look and show what it says. If the POST request was answered with an error (e.g. 500), then have a look at the server's error log as well. — Axel Richter, Oct 03 '16 at 07:08
@axel-richter, the server is a .NET WCF HTTP/JSON service, which logs incoming requests using an endpoint behaviour. The incoming POST request is logged, but there seems to be no data in the body. Which results in a bad request. — RuudSieb, Oct 03 '16 at 07:50
Fortunately (unfortunately for you, but no one should work even with XP in 2016) I have not an XP running to test. But for getting help it would be nice if you would show the whole log informations for the incoming POST. "there seems to be no data in the body" is somewhat mysterious. — Axel Richter, Oct 03 '16 at 08:03
@axel-richter, you're right. As far as I can determine, there **is** no data in the body of the request. I wrote 'seems' because the logging is done in the endpoint behavior, and it could be that the body is discarded in some lower communication layer. Unfortunately I don't have WCF diagnostic logging. — RuudSieb, Oct 03 '16 at 08:30
Maybe you could try without the `mobjWebReq.SetTimeouts...`. See https://msdn.microsoft.com/en-us/library/windows/desktop/aa384086%28v=vs.85%29.aspx#Known_Issues — Axel Richter, Oct 04 '16 at 06:08

score 0 · Accepted Answer · answered Oct 04 '16 at 08:04

Thanks for the replies Axel. I just solved the issue (after pulling my hair out). Apparently this works as expected in Excel 2003:

mobjWebReq.Send (PostData)

And this doesn't (while it does work in Excel 2013)

mobjWebReq.Send PostData

Looks like the PostData is just ignored in the second case, and the issue was not related to the object type. Coincidentally when I used the ServerXMLHTTP object I also used parenthesis.

Use of WinHttpRequest in Excel 2003 under XP

1 Answers1