VBA HTTP Requests

HTTP, or HyperText Transfer Protocol, is the protocol used to request and serve HTML webpages. Requests are sent from a client to a server and the server responds by sending back a webpage to the client. In VBA, HTTP requests can be sent using the MSXML2 library or the WinHttp Services library. It is important for sending HTTP requests to understand HTTP Methods and HTTP Status Codes. HTTP methods determine the type of request that is sent to the server and HTTP status codes indicate the result of the request. In general, GET or POST requests are sent and any response other than 200 OK may require special attention. Tools such as the Browser DevTools in Edge or Chrome, Postman, and Fiddler can be used to view HTTP requests and can help in debugging.

MSXML2

The Microsoft XML library can be used to send HTTP requests.

Public Sub SendXMLHTTPRequest()

    Dim Request  As Object
    Dim HTMLDoc  As Object
    Dim Links    As Object
    Dim Link     As Object
    Dim URL      As String

    URL = "https://docs.microsoft.com/en-us/office/vba/api/overview/"

    Set Request = CreateObject("MSXML2.XMLHTTP.6.0")
    Set HTMLDoc = CreateObject("htmlfile")

    With Request

        .Open "GET", URL
        .send

        Debug.Print .Status, .StatusText
        Debug.Print .getAllResponseHeaders

        HTMLDoc.body.innerHTML = .responseText

    End With

    Set Request = Nothing

    Set Links = HTMLDoc.getElementsByTagName("a")

    For Each Link In Links
        Debug.Print Link.href
    Next Link

    Set Links = Nothing
    Set HTMLDoc = Nothing

End Sub

WinHTTP

The Microsoft WinHTTP Services library can be used to send HTTP requests.

Public Sub SendWinHTTPRequest()

    Dim Request  As Object
    Dim HTMLDoc  As Object
    Dim Links    As Object
    Dim Link     As Object
    Dim URL      As String

    URL = "https://docs.microsoft.com/en-us/office/vba/api/overview/"

    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    Set HTMLDoc = CreateObject("htmlfile")

    With Request

        .Open "GET", URL
        .send

        Debug.Print .Status, .StatusText
        Debug.Print .getAllResponseHeaders

        HTMLDoc.body.innerHTML = .responseText

    End With

    Set Links = HTMLDoc.getElementsByTagName("a")

    For Each Link In Links
        Debug.Print Link.href
    Next Link

    Set HTMLDoc = Nothing
    Set Request = Nothing

End Sub