JSON - Parsing

[JaysonHughes]

So Below is the code that I have but it dose not work.
I have att a image of the data and how it is set out. I wanting to extract "ScannableId" and "quanityItems"

Ideal layout

Pax	QTY
paX22vgib0m	330

Sub getIbTransferManifest()

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.CutCopyMode = False
.StatusBar = True

.Calculation = xlCalculationManual
End With

With CreateObject("WbemScripting.SWbemDateTime")
Dim utc#: .SetVarDate VBA.Now
Let utc = .GetVarDate(False) - VBA.Now
End With

'// REQUIRED USER ADJUSTMENTS
'=========================================================================================================================
Dim site$: Let site = "NCL1" '<----- warehouse id
Dim dest$: Let dest = "Pax" '<----- destination worksheet
'=========================================================================================================================

Dim url$: url = "Midway Authentication Portal"

On Error Resume Next
With ThisWorkbook
.Sheets(dest).Cells.ClearContents: If Err Then Err.Clear: _
.Sheets.Add().Name = dest
Dim ws As Worksheet
Set ws = .Sheets(dest)
End With
On Error GoTo 0

With CreateObject("WinHTTP.WinHTTPRequest.5.1")

'===================== Needed for Authentication =============================

'// http service object properties
.SetAutoLogonPolicy 0
.SetTimeouts 0, 0, 0, 0
.SetClientCertificate "CURRENT_USER\MY\" & Environ$("USERNAME")

'// log into midway to authenticate the http connection
.Open "GET", "Midway Authentication Portal", True
.SetRequestHeader "Cookie", VBAMidway_v1
.Send

'// connect to host; recycle url string variable as response text.
.Open "GET", url, False
.SetRequestHeader "Cookie", VBAMidway_v1
.Send

'===================== Needed for Authentication =============================


.WaitForResponse: Let url = _
.ResponseText

End With

Dim json As Object: Set json = aftParse(url)
Dim hdrs As Object: Set hdrs = CreateObject("Scripting.Dictionary")


With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.CutCopyMode = False
.StatusBar = False
.Cursor = xlNorthwestArrow
.Calculation = xlCalculationAutomatic
End With

End Sub

 

[pjmorris]

In honesty I know nothing about JSON, but if you go to the Data ribbon, on the 'Get & Transform Data' tab you'll see a 'Get Data' button. If you click it you'll have the option to get data 'From File' (amongst other choices), select that and you'll have the option to get from a JSON file. I assume it'll be very useful!!

HTH