0

I am using Excel VBA together with the excellent https://github.com/VBA-tools/VBA-JSON parser which converts a json string to a Dictionary to parse API responses from UPS.

I am getting error messages when parsing the json responses because the responses I get are non-consistent.

For example, a response may look like this:

{"Fault":{"faultcode":"Client", "faultstring":"An exception has been raised as a result of client data.", "detail":{"Errors":{"ErrorDetail":{"Severity":"Hard", "PrimaryErrorCode":{"Code":"9150002", "Description":"Invalid or missing inquiry number - TrackingNumber, ShipmentIdentificationNumber, or ReferenceNumber"}}}}}}

But it can also be:

{"TrackResponse":{"Response":{"ResponseStatus":{"Code":"1", "Description":"Success"}, "TransactionReference":""}, "Shipment":{"InquiryNumber":{"Code":"01", "Description":"ShipmentIdentificationNumber", "Value":"1Z1291249124949"}, "ShipperNumber":"55443322", "ShipmentAddress":[{"Type":{"Code":"01", "Description":"Shipper Address"}, "Address":{"AddressLine":"16 test", "City":"test city", "PostalCode":"23445", "CountryCode":"US"}}, {"Type":{"Code":"02", "Description":"ShipTo Address"}, "Address":{"City":"TEST CITY", "PostalCode":"12345", "CountryCode":"US"}}], "ShipmentWeight":{"UnitOfMeasurement":{"Code":"KGS"}, "Weight":"38.00"}, "Service":{"Code":"011", "Description":"UPS Standard"}, "ReferenceNumber":[{"Code":"13", "Value":"923484MBS"}, {"Code":"16", "Value":"42111"}], "PickupDate":"20210118", "DeliveryDetail":{"Type":{"Code":"03", "Description":"Scheduled Delivery"}, "Date":"20210120", "Time":"235900"}, "Package":[{"TrackingNumber":"1Z609R234234234234", "Activity":{"ActivityLocation":{"Address":{"CountryCode":"US"}}, "Status":{"Type":"M", "Description":"Shipper created a label, UPS has not received the package yet.", "Code":"MP"}, "Date":"20210118", "Time":"104442"}, "PackageWeight":{"UnitOfMeasurement":{"Code":"KGS"}, "Weight":"19.00"}, "ReferenceNumber":{"Code":"19", "Value":"42111"}}, {"TrackingNumber":"1Z23535235235", "Activity":{"ActivityLocation":{"Address":{"CountryCode":"US"}}, "Status":{"Type":"M", "Description":"Shipper created a label, UPS has not received the package yet.", "Code":"MP"}, "Date":"20210118", "Time":"104442"}, "PackageWeight":{"UnitOfMeasurement":{"Code":"KGS"}, "Weight":"19.00"}, "ReferenceNumber":{"Code":"19", "Value":"42111"}}]}}}

So when I try to do:

If json("TrackResponse")("Response")("ResponseStatus")("Description") = "Success" Then
Debug.Print "The response is Success"
End if

and the response was like the first example I get the following error message:

Run-time error 13:
Type mismatch

I do not know how to solve this, as I off course cannot predict what the response will be beforehand.

Any help would be greatly appreciated.

Emil
  • 21
  • 5
  • You code for both and handle the error when it occurs. – Excel Hero Jan 18 '21 at 14:34
  • My VBA based JSON Parser is nimbler and it's a tiny code base compared to VBA-JSON. See this StackOverflow answer: https://stackoverflow.com/a/62820310/3566998 – Excel Hero Jan 18 '21 at 14:37
  • Hi Mr Excel Hero. Thanks for the reply. I have previously used error handling to deal with the different json strings, however, so far I have encountered 4 different formats of the json string so error handling won’t help. I will definitely try your json parser and revert! – Emil Jan 18 '21 at 15:45
  • If you don't know what you will get how are you going to do anything with it? Without knowing *something* about the response it's going to be difficult to handle it programmatically. – Tim Williams Jan 19 '21 at 06:30
  • Hi Tim, thanks for your comment. Well, I know that the response is in json format and I use the json converter to parse it as dictionaries/collections. But, sometimes there are multiple packages and therefore, the dictionaries are created with different keys/items. I guess I could loop through the json string before I parse it to a dictionary but it seems rather clumsy. – Emil Jan 19 '21 at 19:18

0 Answers0