title: Excel VBA/JSON to scrape UPS tracking delivery
body: <p>Thanks to the help and code from @QHarr I have got the tracking info from Fedex, DHL and Startrack working.   I have been trying to use his code and the UPS tracking Web Service Developer Guide and Tracking JSON Developer Guides to get UPS to work as well within Excel.   The JSON converter code is from here <a href="https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas" rel="nofollow noreferrer">https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas</a></p>
  
  <p>The code I have tried is as follows</p>
  
  <pre><code>Public Function GetUPSDeliveryDate(ByVal id As String) As String
      Dim body As String, json As Object
      body = "data={""TrackPackagesRequest"":{""appType"":""WTRK"",""appDeviceType"":""DESKTOP"",""supportHTML"":true,""supportCurrentLocation"":true,""uniqueKey"":"""",""processingParameters"":{},""trackingInfoList"":[{""trackNumberInfo"":{""trackingNumber"":" &amp; Chr$(34) &amp; id &amp; Chr$(34) &amp; ",""trackingQualifier"":"""",""trackingCarrier"":""""}}]}}"
      body = body &amp; "&amp;action=trackpackages&amp;locale=en_AU&amp;version=1&amp;format=json"
  
      With CreateObject("MSXML2.XMLHTTP")
          .Open "POST", "http://wwwapps.ups.com/WebTracking", False
          .setRequestHeader "Referer", "https://www.ups.com/track?loc=en_AU&amp;tracknum=" &amp; id
          .setRequestHeader "User-Agent", "Mozilla/5.0"
          .setRequestHeader "X-Requested-With", "XMLHttpRequest"
          .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
          .send body
          Set json = JSONConverter.ParseJson(.responseText)
      End With
      GetUPSDeliveryDate = Format$(json("ResponseStatus")("ShipmentType")(1)("DeliveryDate"), "dddd, mmm dd, yyyy")
  End Function
  </code></pre>
  
  <p>I am not getting any errors in the code per-say,  but when I use the =GetUPSDeliveryDate() function I am getting a #VALUE! response instead of the delivered date of 7th May 2019, so I am guessing I have got the following bit wrong </p>
  
  <pre><code>    GetUPSDeliveryDate = Format$(json("ResponseStatus")("ShipmentType")(1)("DeliveryDate"), "dddd, mmm dd, yyyy")
  </code></pre>
  
  <p>I have also tried the following, but no luck.</p>
  
  <pre><code>    If json("results")(1)("delivery")("status") = "delivered" Then
           GetUPSDeliveryDate = json("results")(1)("checkpoints")(1)("date")
      Else
          GetUPSDeliveryDate = vbNullString  
      End If
  </code></pre>
  
  <p>A sample UPS tracking number is 1Z740YX80140148107</p>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p>Thanks</p>