Solicitações da Web VBA

Estou construindo uma planilha dinâmica que leva em várias métricas de instância de servidor, que estão disponíveis via API. Em vez de atualizá-los manualmente, o VBA pode fazer um WebRequest para obter os dados e, em seguida, analisar o JSON que é retornado. Com os dados analisados ​​em um objeto, posso iterar sobre ele para preencher as células com os dados apropriados.

As referências estão contidas nos comentários do código

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