Last Updated: September 29, 2021
·
62.59K
· teddy

VBA Web Requests

I am building a dynamic spreadsheet which takes in various server instance metrics, which are available via API. Rather than update these manually, VBA can make a WebRequest to pull the data in and then parse JSON that is returned. With the data parsed into an object, I can iterate over it to fill the cells with the appropriate data.

References are contained within the code comments

Public Function MakeWebRequest(method, url, post_data) As String
    ' 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/

    ' create the request object
    Set req = 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)
    req.SetTimeouts 60000, 60000, 60000, 60000

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

    ' handle post content type
    If method = "POST" Then
        req.SetRequestHeader "Content-type", _
          "application/x-www-form-urlencoded"
    End If


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

    ' set user agent
    req.Option(0) = "Echovoice VBA HTTP Bot v0.1"

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

    ' set redirects
    req.Option(6) = True

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

    ' send request
    ' send post data, should be blank for a get request
    req.Send post_data

    ' read response and return
    MakeWebRequest = req.ResponseText

End Function

Public Function ParseJSON(data As String) As Object
    ' http://www.ediy.co.nz/vbjson-json-parser-library-in-vb6-xidc55680.html
    ' take JSON and convert to object

    ' change code in cStringBuilder for Win64 systems
    ' https://code.google.com/p/vba-json/issues/detail?id=13

    ' add Microsoft Scripting Runtime Reference for Dictionary data type
    ' add ADO reference Microsoft ActiveX Data Objects 2.8 Library
    ' http://msdn.microsoft.com/en-us/library/aa241766(v=vs.60).aspx

    ' use Set when returning an object
    Set ParseJSON = JSON.parse(data)

End Function