CODEWITHSUNDEEP
jsonvbahttpms-accessxmlhttprequest
U70115
U70115

Reputation: 25

Using VB or VBA To Get JSON Responds from Http Request

I just posted my issue with HTTP GET Request using VBA, and I just got it working with the suggestion given to me in a previous post. I took the "Basic" out of the "SetRequestHeader" and I was able to see data. Since my data is in JSON format, I must request the data correctly and storage it to a text file.

In my code, I was setting P = JSON.parse(XMLHttpReq.responseText) being undefine, and what should I define JSON to be in this code??? I was not able to run it because of this issue.

Let me know if anyone see anything wrong too with this, or other suggestions!

I have added some VBA code below to handle the request. 

Sub Test()
    Dim sUrl As String, sAuth As String
    Dim P As Object
    Dim XMLHttpReq As MXXML2.ServerXMLHTTP

    sUrl = "https://api.ngs.nfl.com/tracking/game/play?gameKey=57444&playId=51"
    sAuth = "NGS AKIAIX2CQ7IEOKPOTKDQ:uNniaOp4jH8jcK9i/EtQhurlilc="

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", sUrl, False
        .setRequestHeader "Authorization", "Basic " & sAuth
        .send

        If XMLHttpReq.ReadyState = 4 Then
             If XMLHttpReq.Status = 200 Then

                  ' Process the JSON response here

                  Debug.Print "200 received"
                  Set P = JSON.parse(XMLHttpReq.responseText)
             Else
                  If XMLHttpReq.Status = 404 Then
                       ' Handle it
                  End If
             End If

        Debug.Print .getAllResponseHeaders
    End With
End Sub

Upvotes: 1

Views: 5640

Answers (1)

QHarr
QHarr

Reputation: 84465

For the response I get, which is unauthorised, I used JSONConverter and converted the response text into a JSON object. For the response I got I then show how to access the message returned.

Note: You need to add the JSONConverter .bas to the project and then go VBE > Tools >References and add a reference to Microsoft Scripting Runtime.

Option Explicit   
Public Sub Test()
    Dim sUrl As String, sAuth As String

    Dim XMLHttpReq As MSXML2.ServerXMLHTTP60

    sUrl = "https://api.ngs.nfl.com/tracking/game/play?gameKey=57444&playId=51"
    sAuth = "NGS AKIAIX2CQ7IEOKPOTKDQ:uNniaOp4jH8jcK9i/EtQhurlilc="

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", sUrl, False
        .setRequestHeader "Authorization", sAuth
        .send

        Dim P As Object, key As Variant
        Set P = JsonConverter.ParseJson(.responseText)
        WriteTextFile .responseText
        For Each key In P.Keys
            Debug.Print key & " : " & P(key)
        Next key
    End With
End Sub

Public Sub WriteTextFile(ByVal htmlResponse As String)
    Dim fso As Object, f As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.CreateTextFile("C:\Users\User\Desktop\info.txt", True, True)
    f.Write htmlResponse
    f.Close
End Sub

Edit:

Option Explicit
Public Sub Test()
    Dim sUrl As String, sAuth As String, XMLHttpReq As MSXML2.ServerXMLHTTP60
    sUrl = "https://api.ngs.nfl.com/tracking/game/play?gameKey=57444&playId=51"
    sAuth = "NGS AKIAIX2CQ7IEOKPOTKDQ:uNniaOp4jH8jcK9i/EtQhurlilc="

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", sUrl, False
        .setRequestHeader "Authorization", sAuth
        .send
        WriteTextFile .responseText, "C:\Users\User\Desktop\info.txt"
    End With
End Sub

Public Sub WriteTextFile(ByVal htmlResponse As String, ByVal fileName As String)
    Dim fso As Object, f As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.CreateTextFile(fileName, True, True)
    f.Write htmlResponse
    f.Close
End Sub

Upvotes: 1

Related Questions