MSXML2.ServerXMLHTTP HTTPS "The connection with the server was terminated abnormally "

Question

I have the following test code in VBA in an EXCEL spreadsheet. I am attempting to use MSXML2.ServerXMLHTTP to retrieve JSON from a remote service (the final might require storing and sending cookies to create a session to get the date so I am looking for the most flexible option which seems to be ServerXMLHTTP, I may be wrong here of course)

I was getting the run-time error -2147012866 80072efe "The connection with the server was terminated abnormally " when accessing a site with https

Looking at some other questions suggested that this might be a firewall issue... I turned off the windows firewall to test this and the results were the same.

After some experimentation MSXML2.XMLHTTP worked fine. Also if I access the same site using http I am not getting any errors. In this test code below I am getting the same error for both MSXML2.ServerXMLHTTP and WinHttp.WinHttpRequest

 Sub TestRequest()
 Dim objHttp As New MSXML2.XMLHTTP
 Dim objServHttp As New MSXML2.ServerXMLHTTP
 Dim objWinHttp As New WinHttp.WinHttpRequest
 Dim URL As String
    'URL = "http://api.fixer.io/2017-10-10?base=gbp"
    URL = "https://api.fixer.io/2017-10-10?base=gbp"
   objHttp.Open "GET", URL, False
   On Error GoTo Error_request_1
   objHttp.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
   objHttp.send ("")

Error_request_1:


   'objServHttp.setOption 2, objServHttp.GetOption(2) - SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS 'Ignore Possible certificate errors
   'objServHttp.setOption 2, 13056 'Ignore possible certificate errors
   objServHttp.Open "GET", URL, False
   On Error GoTo Error_request_2
   objServHttp.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
   objServHttp.send ("")

Error_request_2:
    Stop

   objWinHttp.Open "GET", URL, False
   On Error GoTo Error_request_3
   objWinHttp.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
   objWinHttp.send ("")


Error_request_3:
    Stop

End Sub

I'm assuing I'm setting the wrong options, but I'm finding the documentation difficult to source for these...

Greatly appreciate any help trying to get these working for https.

MSXML2.ServerXMLHTTP HTTPS "The connection with the server was terminated abnormally "

0 Answers0