Using HTTPS with Excel VBA

So, no doubt people are familiar with using MSXML2.XMLHTTP60 to make HTTP calls, this can be for web-scraping or even programming against a REST interface. Ideally, you want transport security for these requests but you'll need a different library for that {"Microsoft WinHTTP Services, version 5.1",C:\Windows\system32\winhttp.dll} and you'll need to call SetClientCertificate with a digital certificate.

Digital certificates are a big topic and not for this post, to get going use the Local Machine certificate and suppress warnings, see code below.

To have confidence it is working why not try the HTTP sniffing tool Fiddler, some extra code is added to facilitate this.

Attribute VB_Name = "modHTTPS"
Option Explicit

'* Tools->References: Microsoft WinHTTP Services, version 5.1

Sub Test()

    Dim oXHR As WinHttp.WinHttpRequest
    Set oXHR = New WinHttp.WinHttpRequest
    
    
    'http://stackoverflow.com/questions/1264303/https-post-request-using-vba-for-excel
    Call oXHR.SetClientCertificate("LOCAL_MACHINE\Personal\My Certificate")
    
    '** next line is because we're using the self-generated certificate, in prod purchase a propert certificate
    oXHR.Option(WinHttp.WinHttpRequestOption_SslErrorIgnoreFlags) = WinHttp.WinHttpRequestSslErrorFlags.SslErrorFlag_Ignore_All
        
    Const bUSE_FIDDLER_PROXY As Boolean = False
    If bUSE_FIDDLER_PROXY Then
        Const HTTPREQUEST_PROXYSETTING_PROXY As Long = 2
        oXHR.SetProxy HTTPREQUEST_PROXYSETTING_PROXY, "127.0.0.1:8888", ""
    End If
    
    Call oXHR.Open("GET", "https://www.google.co.uk", False)
    
    oXHR.Send
    
    oXHR.WaitForResponse

    Debug.Print oXHR.ResponseText
    Stop

End Sub