I try to extract JSON data from an API to Excel table using VBA.
I have installed VBA-JSON and turned on Microsoft Scripting Runtime.
The json request delivers:
[ { "Itemcode": "6FSTGWD40", "DeliveryTimeInDays": 999, "PalletQuantity": 12, "Description": "Fiberstone", "ItemDescription_NL": "Fiberstone", "ItemStatus": "E", "SalesPackage_NL": "Stuks ", "SalesPackage_DE": "Stück", "SalesPackage_EN": "Unit", "SalesPackage_FR": "Pièce", "Salesprice": 132, "MainGroupCode": "200", "MainGroupDescription_NL": "Hardware", "MainGroupDescription_DE": "Hardware", "MainGroupDescription_EN": "Hardware", "MainGroupDescription_FR": "Hardware", "ProductGroupCode": "300", "ProductGroupDescription_NL": "Plantenbakken", "ProductGroupDescription_DE": "Gefässe", "ProductGroupDescription_EN": "Planters", "ProductGroupDescription_FR": "Bacs", "GroupDescription": "FSTC", "GroupDescription_NL": "Pottery Pots Fiberstone", "GroupDescription_DE": "Pottery Pots Fiberstone", "GroupDescription_EN": "Pottery Pots Fiberstone", "GroupDescription_FR": "Pottery Pots Fiberstone", "MaterialGroupCode": "500", "MaterialGroupDescription_NL": "Composiet", "MaterialGroupDescription_DE": "Verbundwerkstoff", "MaterialGroupDescription_EN": "Composite", "MaterialGroupDescription_FR": "Matériau composite", "ItemDescription_EN": "Fiberstone", "ItemDescription_DE": "Fiberstone", "ItemDescription_FR": "Fiberstone", "GTINCode": "8717775668411", "IsStockItem": true, "Warehouse": "A", "ItemVariety_NL": "Dice XL Glossy White", "ItemVariety_DE": "Dice XL Glossy White", "ItemVariety_EN": "Dice XL Glossy White", "ItemVariety_FR": "Dice XL Glossy White", "PotSize": null, "ItemPictureName": "6FSTGWD40.png", "ItemPictureSysmodified": "2019-03-04T11:51:24", "Content_Ltr": null, "PlantPassportCode": null, "Diameter": 46, "Length": 0, "Width": 0, "Height": 60, "Depth": 59, "Opening": 36, "IsOffer": false, "ShowOnWebsite": false, "Sysmodified": "2021-07-13T07:38:08.853", "SalesOrderSize": 1, "Tags": [ { "Code": "Brand", "Values": [ { "Description_NL": "Pottery Pots", "Description_DE": "Pottery Pots", "Description_EN": "Pottery Pots", "Description_FR": "Pottery Pots" } ] }, { "Code": "Collection", "Values": [ { "Description_NL": "Essential", "Description_DE": "Essential", "Description_EN": "Essential", "Description_FR": "Essential" } ] }, { "Code": "ColourPlanter", "Values": [ { "Description_NL": "Wit", "Description_DE": "Weiß", "Description_EN": "White", "Description_FR": "Blanche" } ] }, { "Code": "Finish", "Values": [ { "Description_NL": "Hoogglans", "Description_DE": "Hochglanz", "Description_EN": "High gloss", "Description_FR": "Brillant" } ] }, { "Code": "Location", "Values": [ { "Description_NL": "Buiten", "Description_DE": "Draußen", "Description_EN": "Outdoor", "Description_FR": "Extérieur" }, { "Description_NL": "Binnen", "Description_DE": "Innen", "Description_EN": "Indoor", "Description_FR": "Intérieur" } ] }, { "Code": "Material", "Values": [ { "Description_NL": "Fiberstone", "Description_DE": "Fiberstone", "Description_EN": "Fiberstone", "Description_FR": "Fiberstone" } ] }, { "Code": "MaterialProperties", "Values": [ { "Description_NL": "Handgemaakt", "Description_DE": "Handgefertigt", "Description_EN": "Handmade", "Description_FR": "Fait à la main" } ] }, { "Code": "Serie", "Values": [ { "Description_NL": "Fiberstone", "Description_DE": "Fiberstone", "Description_EN": "Fiberstone", "Description_FR": "Fiberstone" } ] }, { "Code": "Shape", "Values": [ { "Description_NL": "Cylinder", "Description_DE": "Cylinder", "Description_EN": "Cylinder", "Description_FR": "Cylinder" } ] }, { "Code": "Structure", "Values": [ { "Description_NL": "Egaal", "Description_DE": "Glatt", "Description_EN": "Smooth", "Description_FR": "Lisse" } ] } ] }, { "Itemcode": "8EE425140", "DeliveryTimeInDays": 2, "PalletQuantity": 288, "Description": "Begonia maculata", "ItemDescription_NL": "Begonia maculata", "ItemStatus": "A", "SalesPackage_NL": "Stuks ", "SalesPackage_DE": "Stück", "SalesPackage_EN": "Unit", "SalesPackage_FR": "Pièce", "Salesprice": 8.45, "MainGroupCode": "200", "MainGroupDescription_NL": "Hardware", "MainGroupDescription_DE": "Hardware", "MainGroupDescription_EN": "Hardware", "MainGroupDescription_FR": "Hardware", "ProductGroupCode": "600", "ProductGroupDescription_NL": "Artificial ", "ProductGroupDescription_DE": "Artificial ", "ProductGroupDescription_EN": "Artificial ", "ProductGroupDescription_FR": "Artificielle", "GroupDescription": "KUC", "GroupDescription_NL": "Artificial decoration", "GroupDescription_DE": "Artificial Dekoration", "GroupDescription_EN": "Artificial decoration", "GroupDescription_FR": "Décoration artificielles", "MaterialGroupCode": null, "MaterialGroupDescription_NL": null, "MaterialGroupDescription_DE": null, "MaterialGroupDescription_EN": null, "MaterialGroupDescription_FR": null, "ItemDescription_EN": "Begonia maculata", "ItemDescription_DE": "Begonia maculata", "ItemDescription_FR": "Begonia maculata", "GTINCode": "8714344320619", "IsStockItem": true, "Warehouse": "A", "ItemVariety_NL": "Tak", "ItemVariety_DE": "Zweig", "ItemVariety_EN": "Branch", "ItemVariety_FR": "Branche", "PotSize": "0", "ItemPictureName": "8EE425140.png", "ItemPictureSysmodified": "2020-02-11T08:57:17", "Content_Ltr": null, "PlantPassportCode": null, "Diameter": 0, "Length": 0, "Width": 0, "Height": 120, "Depth": 0, "Opening": 0, "IsOffer": false, "ShowOnWebsite": true, "Sysmodified": "2021-07-06T07:38:14.623", "SalesOrderSize": 1, "Tags": [ { "Code": "ArtificialGroup", "Values": [ { "Description_NL": "Overig", "Description_DE": "Andere", "Description_EN": "Other", "Description_FR": "Autres" } ] }, { "Code": "ArtificialType", "Values": [ { "Description_NL": "Tak", "Description_DE": "Ast", "Description_EN": "Branch", "Description_FR": "Branche" } ] }, { "Code": "ArtificialVariety", "Values": [ { "Description_NL": "Planten", "Description_DE": "Pflanzen", "Description_EN": "plants-artificial", "Description_FR": "Plantes" } ] } ] } ] With the following code I can write the items Itemcode, DeliveryTimeInDays, PalletQuantity and so on in specific Excel cells.
I don't get it to work to write the "Tags" with "Code" and "Values" in Excel cells.
Sub test_json() ' dieses makro ist der startpunkt: einholen von aid, ean etc. dieses makro ruft eine liste aller artikel mit status ab. Worksheets("test").Range("a1:zz100000").ClearContents Dim ws As Worksheet, jsonObject As Object, jsonText As String, i As Long, http As Object i = 3 'requesturl = "https://customerapi_dev.nieuwkoop-europe.com/items?sysmodified=2000-01-01" requesturl = "https://customerapi_dev.nieuwkoop-europe.com/items?sysmodified=2021-02-11&itemCode=6PPNLBO80" Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", requesturl, False http.send Set jsonObject = JsonConverter.ParseJson(http.responseText) Worksheets("test").Cells(1, 1).Value = "Itemcode" Worksheets("test").Cells(1, 2).Value = "DeliveryTimeInDays" Worksheets("test").Cells(1, 3).Value = "PalletQuantity" For Each Item In jsonObject Worksheets("test").Cells(i, 1).Value = Item("Itemcode") Worksheets("test").Cells(i, 2).Value = Item("DeliveryTimeInDays") Worksheets("test").Cells(i, 3).Value = Item("PalletQuantity") i = i + 1 Next Item End Sub I tried as loop code like the following but I always get an error.
For Each Item In jsonObject ("Tags")("Code")("Values") Worksheets("test").Cells(i, 5).Value = Item("Description_NL") Worksheets("test").Cells(i, 6).Value = Item("Description_DE") Worksheets("test").Cells(i, 7).Value = Item("Description_FR") i = i + 1 Next Item How can I write the "Tags" data (for instance "Code" and values like Description_NL) from JSON file in the Excel cells too?
