Problem mit JSON
29.06.2021 10:43:38
Rheinblitz
ich möchte gerne Sendungsdaten von einer Webseite auslesen und den jeweiligen Sendungsstatus in meiner Excel-Datei hinter der Sendungsnummer vermerken.
Sub GetStatusDHL()
Const sourceCol As Long = 1
Const destCol As Long = 2
Dim Parsed As Dictionary
Dim trackingNumberDHL As String
Dim Json As String
Dim deliveryState As String
Dim ws As Worksheet
Dim firstRowMin As Long
Dim firstRow As Long
Dim lastRow As Long
Dim currRow As Long
Set ws = ActiveSheet
firstRow = Selection.Row
lastRow = Selection.Row + Selection.Rows.Count - 1
firstRowMin = 2
If firstRow "" Then
Set Parsed = JsonConverter.ParseJson(Json)
On Error Resume Next
deliveryState = Parsed("results")(1).Item("shipments")(1).Item("statusCode")
On Error GoTo 0
If deliveryState = "" Then
On Error Resume Next
deliveryState = "HTTP-Status: " & Parsed("errors")(1).Item("code") & " ("
deliveryState = deliveryState & Parsed("errors")(1).Item("label") & " - "
deliveryState = deliveryState & Parsed("errors")(1).Item("message") & ")"
On Error GoTo 0
End If
Else
deliveryState = "Fetching data failed"
End If
ws.Cells(currRow, destCol) = deliveryState
Set Parsed = Nothing
deliveryState = ""
Next currRow
End Sub
Die Daten werden mittels GetJSON von der Webseite erfolgreich extrahiert. Nachfolgend ein Beispiel des Inhalts:
{"shipments":[{"id":"123071891","service":"express","origin":{"address":{"addressLocality":"LEEDS - UK"}},"destination":{"address":{"addressLocality":"MILAN - ITALY"}},"status":{"timestamp":"2021-06-17T09:55:00","location":{"address":{"addressLocality":"MILAN - ITALY"}},"statusCode":"delivered","status":"delivered","description":"Delivered"},"details":{"proofOfDelivery":{"timestamp":"2021-06-17T09:55:00","signatureUrl":"https://webpod.dhl.com/webPOD/DHLePODRequest?hwb=lPA4fdgvR162HuUWND814A%3D%3D&pudate=mR1qcfZA0%2FzjvGMovV5Tew%3D%3D&appuid=mgprI8u9CZw%2F78GlC3HsIg%3D%3D&language=en&country=G0","documentUrl":"https://webpod.dhl.com/webPOD/DHLePODRequest?hwb=lPA4fdgvR162HuUWND814A%3D%3D&pudate=mR1qcfZA0%2FzjvGMovV5Tew%3D%3D&appuid=mgprI8u9CZw%2F78GlC3HsIg%3D%3D&language=en&country=G0"},"totalNumberOfPieces":1,"pieceIds":["JD014600008777087249"]},"events":[{"timestamp":"2021-06-17T09:55:00","location":{"address":{"addressLocality":"MILAN - ITALY"}},"description":"Delivered"},{"timestamp":"2021-06-17T09:08:00"
,"location":{"address":{"addressLocality":"MILAN - ITALY"}},"description":"With delivery courier"},{"timestamp":"2021-06-17T07:29:00","location":{"address":{"addressLocality":"MILAN - ITALY"}},"description":"Arrived at Delivery Facility in MILAN - ITALY"},{"timestamp":"2021-06-17T06:27:00","location":{"address":{"addressLocality":"MILAN - MALPENSA - ITALY"}},"description":"Departed Facility in MILAN - MALPENSA - ITALY"},{"timestamp":"2021-06-17T03:56:00","location":{"address":{"addressLocality":"MILAN - MALPENSA - ITALY"}},"description":"Processed at MILAN - MALPENSA - ITALY"},{"timestamp":"2021-06-17T03:53:00","location":{"address":{"addressLocality":"MILAN - MALPENSA - ITALY"}},"description":"Clearance processing complete at MILAN - MALPENSA - ITALY"},{"timestamp":"2021-06-17T02:03:00","location":{"address":{"addressLocality":"MILAN - MALPENSA - ITALY"}},"description":"Arrived at Sort Facility MILAN - MALPENSA - ITALY"},{"timestamp":"2021-06-16T23:57:00","location":{"address":{"addressLocality":"MILAN - MA
LPENSA - ITALY"}},"description":"Customs status updated"},{"timestamp":"2021-06-16T22:46:00","location":{"address":{"addressLocality":"EAST MIDLANDS - UK"}},"description":"Departed Facility in EAST MIDLANDS - UK"},{"timestamp":"2021-06-16T22:04:00","location":{"address":{"addressLocality":"MILAN - MALPENSA - ITALY"}},"description":"Customs status updated"},{"timestamp":"2021-06-16T19:43:00","location":{"address":{"addressLocality":"EAST MIDLANDS - UK"}},"description":"Arrived at Sort Facility EAST MIDLANDS - UK"},{"timestamp":"2021-06-16T19:35:00","location":{"address":{"addressLocality":"EAST MIDLANDS - UK"}},"description":"Processed at EAST MIDLANDS - UK"},{"timestamp":"2021-06-16T17:58:00","location":{"address":{"addressLocality":"LEEDS - UK"}},"description":"Departed Facility in LEEDS - UK"},{"timestamp":"2021-06-16T16:29:00","location":{"address":{"addressLocality":"LEEDS - UK"}},"description":"Processed at LEEDS - UK"},{"timestamp":"2021-06-16T15:20:00","location":{"address":{"addressLocality":"LEEDS -
UK"}},"description":"Shipment picked up"}]}],"possibleAdditionalShipmentsUrl":["/track/shipments?trackingNumber=123071891&service=freight","/track/shipments?trackingNumber=123071891&service=dgf","/track/shipments?trackingNumber=123071891&service=ecommerce","/track/shipments?trackingNumber=123071891&service=parcel-de","/track/shipments?trackingNumber=123071891&service=parcel-nl","/track/shipments?trackingNumber=123071891&service=parcel-pl"]}
in mein Excelblatt möchte ich aus dem obigen String den "statusCode" einfügen; das funktioniert leider nicht wie gewünscht.
Ich denke der Schlüssel liegt in "deliveryState = Parsed("results")(1).Item("shipments")(1).Item("statusCode")", aber ich komme seit Tagen nicht weiter. Ich bin über jede Hilfe dankbar :)