HTTPS POST request using VBA for Excel

Question

I use "WinHttp.WinHttpRequest.5.1" to send HTTP POST requests from VBA in Excel. But I could not manage to do it for HTTPS, as I received an SSL certificate error.

What VBA code would you use to negotiate an SSL connection to a website from VBA in Excel ?

Answer 1

The WinHttpRequest object has a SetClientCertificate method. Try this code example taken from the MSDN (I tried to adapt it for VBA):

' Instantiate a WinHttpRequest object. '
Dim HttpReq as new ActiveXObject("WinHttp.WinHttpRequest.5.1")

' Open an HTTP connection. '
HttpReq.Open("GET", "https://www.test.com/", false)

' Select a client certificate. '
HttpReq.SetClientCertificate("LOCAL_MACHINE\Personal\My Certificate")

' Send the HTTP Request. '
HttpReq.Send()

Answer 2

While I have not used the COM component (WinHttpRequest), it seems you need a call to SetClientCertificate prior to calling send, as per the link.

Does that help?

Answer 3

I have the same situation (send a http request from a VBA in Excel); I created three objects:

Set HttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

-- for the http request class, and

Set fsobj = CreateObject("Scripting.FileSystemObject") Set txtobj = fso.OpenTextFile("C:\PKCERT.PEM")

-- to get into a variable the certificate contents, to pass it to HttpReq.SetClientCertificate,

certificate_data = txtobj.ReadAll HttpReq.SetClientCertificate (certificate_content)

So I can send the request including its public key certificate, as usual, HttpReq.Send

P.S. I found a script at http://www.808.dk/?code-simplewinhttprequest -- it worked fine in my case, hope in yours too.