Using VB or VBA To Get JSON Responds from Http Request

Question

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

If you need to convert the data into 2d array or display it on the sheet then take a look at this solution. — omegastripes
– omegastripes, Commented Jul 3, 2018 at 10:27

QHarr · Accepted Answer · 2018-06-15 20:24:41Z

1

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

edited Jun 15, 2018 at 20:24

answered Jun 14, 2018 at 20:20

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

U70115 Over a year ago

You indicated errors, you are correct. I took out the key word "Basic" from the .SetRequestHeader "Authorization", sAuth That was the key to get results. I See the results when I do a Debug.Print ResponseText. The data that I see look like this: 31:13.800","x":34.22,"y":46.31,"z":0,"s":0,"a":0,"dis":0,"o":1.61,"dir":294.06,"isOnField":true},{"time":"2017-12-17T01:31:13.900","x":34.22,"y":46.32,"z":0,"s":0,"a":0,"dis":0,"o":1.62,"dir":294.85,"isOnField":true}, I am continuing

U70115 Over a year ago

My previous comment was example of what I got from using the Debug.Print ResponseText Is there a way without using JSON to get all of the data and have it on a file??? That will work for me, I just like to copy and paste them into txt files. I did this manually connecting and copying from the response body window. The data I see is not from the start. What do you recommend? Is there a way to get the start and all of the content?? Or must I get small groups at a time?? I have also downloaded VBA-JSON V2.3.0 Is this the version you used?

U70115 Over a year ago

Can you assist me with writing the output to text file?? Or must I create a new post??? Thank You

QHarr Over a year ago

I have added an example of writing out to a text file. You could modify the bottom sub to accept a second argument which could be the save as filename as opposed to the hard coded current filename.

QHarr Over a year ago

Did that make sense?

|

Collectives™ on Stack Overflow

Using VB or VBA To Get JSON Responds from Http Request

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related