向加拿大統計局發布請求 API。我得到的回應字串很復雜（至少對我而言），我無法從中提取任何值。
嘗試了許多可能只獲得“cansimId”的語法但沒有成功。
我的目標是列出（陣列回圈）“dimensionPositionId”：2（“主要統計資料”）并獲取全部（“memberNameEn”）
非常感謝！:)
這是回應中的示例。太大了，無法在此處全部發布 100K.txt：
[
{
"status": "SUCCESS",
"object": {
"responseStatusCode": 0,
"productId": "16100047",
"cansimId": "304-0014",
"cubeTitleEn": "Manufacturers' sales, inventories, orders and inventory to sales ratios, by industry (dollars unless otherwise noted)",
"cubeTitleFr": "Stocks, ventes, commandes et rapport des stocks sur les ventes pour les industries manufacturières, selon l'industrie (dollars sauf indication contraire)",
"cubeStartDate": "1992-01-01",
"cubeEndDate": "2021-08-01",
"frequencyCode": 6,
"nbSeriesCube": 2798,
"nbDatapointsCube": 935808,
"releaseTime": "2021-10-14T08:30",
"archiveStatusCode": "2",
"archiveStatusEn": "CURRENT - a cube available to the public and that is current",
"archiveStatusFr": "ACTIF - un cube qui est disponible au public et qui est toujours mise a jour",
"subjectCode": [
"1699",
"230402",
"330303",
"451003"
],
"surveyCode": [
"2101"
],
"dimension": [
{
"dimensionPositionId": 1,
"dimensionNameEn": "Geography",
"dimensionNameFr": "Géographie",
"hasUom": false,
"member": [
{
"memberId": 1,
"parentMemberId": null,
"memberNameEn": "Canada",
"memberNameFr": "Canada",
"classificationCode": "11124",
"classificationTypeCode": "1",
"geoLevel": 0,
"vintage": 2016,
"terminated": 0,
"memberUomCode": null
}
]
},
{
"dimensionPositionId": 2,
"dimensionNameEn": "Principal statistics",
"dimensionNameFr": "Les statistiques principales",
"hasUom": true,
"member": [
{
"memberId": 1,
"parentMemberId": null,
"memberNameEn": "Sales of goods manufactured (shipments)",
"memberNameFr": "Ventes de biens fabriqués (livraisons)",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 2,
"parentMemberId": null,
"memberNameEn": "New orders, estimated values of orders received during month",
"memberNameFr": "Nouvelles commandes, valeur estimative des commandes re?ues durant le mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 3,
"parentMemberId": null,
"memberNameEn": "Unfilled orders, estimated values of orders at end of month",
"memberNameFr": "Commandes en carnet, valeur estimative des commandes à la fin du mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 4,
"parentMemberId": null,
"memberNameEn": "Raw materials, fuel, supplies, components, estimated values at end of month",
"memberNameFr": "Matières premières, combustibles, fournitures et composantes, valeur estimative à la fin du mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 5,
"parentMemberId": null,
"memberNameEn": "Goods or work in process, estimated values at end of month",
"memberNameFr": "Biens en cours de fabrication ou travaux en cours, valeur estimative à la fin du mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 6,
"parentMemberId": null,
"memberNameEn": "Finished goods manufactured, estimated values at end of month",
"memberNameFr": "Produits finis fabriqués, valeur estimative à la fin du mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 7,
"parentMemberId": null,
"memberNameEn": "Total inventory, estimated values of total inventory at end of the month",
"memberNameFr": "Total des stocks, valeur estimative des stocks à la fin du mois",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 81
},
{
"memberId": 8,
"parentMemberId": null,
"memberNameEn": "Ratio of total inventory to sales",
"memberNameFr": "Rapport du total des stocks aux ventes",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 270
},
{
"memberId": 9,
"parentMemberId": null,
"memberNameEn": "Ratio of finished goods to sales",
"memberNameFr": "Rapport des produits finis aux ventes",
"classificationCode": null,
"classificationTypeCode": null,
"geoLevel": null,
"vintage": null,
"terminated": 0,
"memberUomCode": 270
}
]
},
這是代碼：
Dim Request As New MSXML2.XMLHTTP60
Dim Body As String
Dim Json As Object
Body = "[{" & Chr(34) & "productId" & Chr(34) & ":16100047}]"
Request.Open "POST", "https://www150.statcan.gc.ca/t1/wds/rest/getCubeMetadata", False
Request.SetRequestHeader "Content-Type", "application/json"
Request.send Body
Set Json = JsonConverter.ParseJson(Request.ResponseText)
'Debug.print Json("status") 'Error !!!!
'Debug.Print Json("cansimId") 'Error!!!!
'Debug.print Json("object")("cansimId") 'Error!!!!
uj5u.com熱心網友回復：
謝謝托馬拉克，你是對的！一件事......我所有的陣列似乎都是'option base 1'（雖然沒有指定！）所以 Json(1)("status") 現在正在作業。
終于得到了我需要的東西：
Dim ColStatPrinc As Collection
Dim StatPrinc As Dictionary
x = 1
Set ColStatPrinc = Json(1)("object")("dimension")(2)("member")
With Sheets(1)
For Each StatPrinc In ColStatPrinc
.Cells(x, 1) = StatPrinc("memberId")
.Cells(x, 2) = StatPrinc("memberNameFr")
x = x 1
Next StatPrinc
End With
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/345285.html