Using VBA to convert JSON, with multi-level dictionaries

oitbc

New Member
Joined
Mar 11, 2019
Messages
12
Hi All,

I am trying to use the NVD database JSON files (available to download from https://nvd.nist.gov/vuln/data-feeds - ) so I can construct a sheet of all the vulnerailities and then I can sort them in Excel. Yes, I realise that NVD does offer a search function so I could limit it to Oracle or SAP, but then I have to go into each vulnerability to find out the score and the attack paths etc. The JSON file contains it all (I am using the recent file)

I found https://codingislove.com/excel-json/ and used their .bas file and turned on the ms scripting flag and had some success. I was able to pull out the ID, the explanation and the time of submisison and update. Then I hit an issue. Its to do with the fact that I need to check the vendor name and sometimes vendor name is there and sometimes it isnt. So I did some more research and found https://stackoverflow.com/questions/21936044/checking-if-a-nested-dictionary-key-exists-in-vba which showed me how to handle nested .exists statements, except I can't get it to work for this particular one. It keeps erroring with Error 05. Essentially it works for the first 3 entries as they have a vendor_name, but item 4 does not, although it has a vendor_data which is the level above. I've tried numerous if checks with a .exists but it just won't seem to take.

Code:
Sub getJSON()

Dim FSO As New FileSystemObject
Dim JsonTS As TextStream

Set JsonTS = FSO.OpenTextFile("C:/Users/pt/Documents/ExcelJSON/test.json", ForReading)
JsonText = JsonTS.ReadAll
JsonTS.Close
    
Set Json = ParseJson(JsonText)

i = 1

For Each Item In Json("CVE_Items")

Worksheets("Sheet1").Cells(i, 1).Value = Item("cve")("CVE_data_meta")("ID")
'Worksheets("Sheet1").Cells(i, 2).Value = Item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")  <--- this errors if vendor_name does not exist, so I tried an if

'MsgBox TypeName(Item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")) <---- also errors if vendor_name does not exist, otherwise returns vendor name

'If Not Item("cve")("affects")("vendor")("vendor_data")(1).Exists("vendor_name") Then <--- check to see if it doesnt exist doesnt work

If Item("cve")("affects")("vendor")("vendor_data").Exists("vendor_name") Then  ' <----works for 1st 3 items in list as they have vendor_names then it crashes as 4th does not have vendor name
Worksheets("Sheet1").Cells(i, 2).Value = Item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")
End If
Worksheets("Sheet1").Cells(i, 3).Value = Item("cve")("description")("description_data")(1)("value") <--- works
Worksheets("Sheet1").Cells(i, 4).Value = Item("publishedDate") <--- works
Worksheets("Sheet1").Cells(i, 5).Value = Item("lastModifiedDate") <--- works

i = i + 1
Next

End Sub
The JSON schema I have checked with http://json.parser.online.fr/

one without looks like this

Code:
"cve":{
[LIST]
[*]"data_type":"CVE", 
[*]"data_format":"MITRE", 
[*]"data_version":"4.0", 
[*]"CVE_data_meta":{
[LIST]
[*]"ID":"CVE-2017-3164", 
[*]"ASSIGNER":"cve@mitre.org" 
[/LIST]
}, 
[*]"affects":{
[LIST]
[*]"vendor":{
[LIST]
[*]"vendor_data":[] 
[/LIST]
  
[/LIST]
  
[/LIST]
and a good one looks like this

Code:
"cve":{
[LIST]
[*]"data_type":"CVE", 
[*]"data_format":"MITRE", 
[*]"data_version":"4.0", 
[*]"CVE_data_meta":{
[LIST]
[*]"ID":"CVE-2017-12447", 
[*]"ASSIGNER":"cve@mitre.org" 
[/LIST]
}, 
[*]"affects":{
[LIST]
[*]"vendor":{
[LIST]
[*]"vendor_data":[
[LIST=1]
[*]{
[LIST]
[*]"vendor_name":"gnome", 
[/LIST]
  
[/LIST]
  
[/LIST]
  
[/LIST]
  
[/LIST]

[/CODE]
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,323
Are you still stuck on this? I think the problem is that the vendor_data item exists, but its associated array (collection) is empty, signified by the empty square brackets:

"vendor_data" : [ ]

Here's one way to handle this:

Code:
        If item("cve")("affects")("vendor")("vendor_data").Count > 0 Then
            Worksheets("Sheet1").Cells(i, 2).Value = item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")
        End If
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,844
Messages
5,489,221
Members
407,681
Latest member
HoneyBadger914

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top